We have a spreadsheet of our customer's info, to send to our collection agency; when we export it puts parenthesis around the area code and adds a hyphen in the number.
Also it puts commas in the figures. And I need the slashes gone from the date - is there a simple way to do this?
I tried formatting but it doesn't take out the () or ,
I think your best method is to use excel's 'Replace' function.
Basically on the worksheet, hold down CTRL and press H.
This will open up the 'Find & Replace' function. So in the first box, type in '(' or ')' or whatever you need to remove - without the apostrophe - and in the second box, leave it blank. Now press 'Replace all'.
That should work for you
Edit: Also, to get rid of the commas from your numbers, highlight the fields with commas, right click and select 'Format cells'. Click on 'Number' and then untick the 'Use 1000 separator'. Press OK.
Last edited by Reapz; 05-28-2014 at 01:22 PM. Reason: Additional info
that did work - thank you!!
but for some reason it is not taking out the commas - this is exported from Epicor software, wonder if they are another kind of key?
Commas are formatting issue. Change format to General and commas will go away.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Commas in the cells like G are actually number formatting, and not hard-written in there! If you right click, Format Cells, and then you should see the number formatting options.
Going for Guru! Click the Star to the bottom left of this post if I helped!
Ok thank you!!!!!
So when i exported the file, I formatted the dates, as they were like 8/2/2012 instead of 08/02/2012. Then i went to remove the / and \, but when i did that it took out the zeros it had in there. Is there a way to get that back without the / and \??
Again, unless dates are text, the "/" is just a formatting. You have to change format to Custom and type in mmddyyyy or if you prefer ddmmyyy
ok got it - thank you - custom worked
i need the dates to show without the / or \
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks