Anybody here good with Excel Macros??

[jhzero1984]

Hello All,

I have been working on an excel data file that is exported from database. The problem is, the file lists numbers in the format that looks like this:
' ######### (apostrophe, five spaces, then a bunch of numbers)

However, to run a comparison with another data sheet, I need to remove the apostrophe and five spaces, then go back and hit F2 to format/edit the data so that I can use it in comparison (VLOOKUP). There are four hundred #'s in a column like this, and I have to edit each one. Is there some macro that I can create to edit this data much faster?


I do have one trick to get rid of the apostrophe, which is copy, paste special, values in the next column, then delete the original data with the apostrophe, then copy over the paste special data. However, I have not figured out a way to remove the five spaces easily. Replacing the spaces with zeros would also work, since I can always format the cells to numbers, dropping the zeros.


Any help is much appreciated.


Zero

 

[cuemasterfl]

Could you do a simple search/replace?

Search for: , (apostrophe then 5 spaces)
Replace with: (blank)

 

[jhzero1984]

Find and replace works with the blank spaces, which is good enough for me. I can't believe I did not think of that. Thank You.


Zero

 

[cuemasterfl]

You're welcome

 

[chiron]

Find and replace works with the blank spaces, which is good enough for me. I can't believe I did not think of that. Thank You.


Zero

It just can't be that simple.

I know that feeling only too well.