Parsing Data (Excel 2003 SP3)

Topic

New Reply

I have a column of data in a spreadsheet that is comprised of numbers and text and symbols the format is “xxx77xxx – xxxxxxxxxxxx”. The length of the column is variable. WHat I am needing is to be able to extract all data to the right (or following) of ” – “, the “space,hyphen,space” is the only characters unique to each record.

Reply | Quote

Replies

Let’s say that your data are in A2 and down (A1 is a column header).
Enter the following formula in B2:

=MID(A2,FIND(" - ",A2)+3,100)

Then fill down as far as needed. The number 100 in the formula is merely a number larger than the longest text string you expect to encounter; if you’re sure that the xxxxxxxxxxxx part at the end will never be more than 20 characters long, you can use 20 instead of 100.

Reply | Quote

Thanks Hans, I was doing this but was using too small of number on the field length – thanks again

Reply | Quote