separating data in cells
Posted by Denise Horn on May 10, 2001 11:45 AM
I have a list of names and numbers that I copied from Acrobat to Excel and each line came over in one cell. I need to remove the numbers from the cells and put them in a column by themselves. I tried setting up a macro to insert a delimiter in each cell so that I could parse the information but when I run the macro, it copies the information from the original cell to the row below it. Can you help me with this?
Posted by Barrie Davidson on May 10, 2001 11:56 AM
Hi Denise, why not try parsing the data using Excel's data function (assuming the data has fields with constand width). If you select the data you want parsed, select Data|Text to Columns from the menu and follow the prompts.
If you need help with it let me know (via this board).
Barrie
Posted by Dave Hawley on May 10, 2001 11:57 AM
Hi Denise
You are probably looking for the "Text To columns" function. This can be found under Data on the main menu. If no good for your purpose, show me some sample data and i will write you a macro, Custum function or nest some of excels standard formulas.
Dave
OzGrid Business Applications
Posted by Denise Horn on May 10, 2001 12:36 PM
Posted by Denise Horn on May 10, 2001 12:38 PM
Barrie,
The text in each row has a different number of characters in each -- the last four characters are all numeric; those are the one's I need in a separate column.
Denise
Posted by Dave Hawley on May 10, 2001 12:47 PM
Hi again Denise
Use =VALUE(RIGHT(A1,4))
Copy down as far as needed, then copy and PasteSpecial as Values.
Dave
OzGrid Business Applications
Posted by Barrie Davidson on May 10, 2001 12:53 PM
Dave, you are too quick!!
Dave, in case you take the subject line the wrong way, I'm just giving you a compliment (better to be safe than sorry I always say).
Posted by Denise Horn on May 10, 2001 1:02 PM
Re: Dave, you are too quick!!
Thanks guys!!! The =value formula worked.
Denise
Posted by Lusty Lady on May 10, 2001 1:03 PM
How about removing X number of charcters from the left side using the LEFT and LEN functions. For example =LEFT(A1,(LEN(A1)-4)).
Then only show X number of characters from the right using the RIGHT function. For example =RIGHT(A1,4).
Then you don't have to disturb your original data. You can set your print area to print just those cells that you need for your report.
Posted by Dave Hawley on May 10, 2001 1:18 PM
Re: Dave, you are too quick!!
Thanks Barry, I thought as much coming from you.
Cheers
Dave
OzGrid Business Applications
