separate text and numbers

**dvandaniker** · 08-22-2007, 07:33 PM

I need help creating a formula to separate the text from the numbers into 2 separate columns.

Examples are:
A1= Angel Romero 260.00
A2= Wieben Chiropractic Clinic 74.00
A3= R Ricardo Ramirez Dds 340.00

The 'Text to Column' function does not work because there is no fixed width and no deliminater. To add in a deliminater, like a "\", is an option but there are thousands of cells to do this to.

As you can see, using LEFT, RIGHT and MID functions become tricky since the deliminater would be a "space" but there are often several "spaces" in the string of characters.

Is there a way to SEARCH or FIND the first number and let that be the deliminater?

Thanks, Derek

**Ron Coderre** · 08-22-2007, 08:03 PM

This might be the easiest approach....

With A1 containing text (ending with a space and numbers)

Try this
B1: =SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))
Copy that formula down as far as you need

If A1: Angel Romero 260.00
Then B1 returns: Angel Romero|260.00

Copy the Col_B formulas....<edit><paste special>...Check: Values...Click [OK]

That will hardcode the formula results
Last, <data><text-to-columns>...Delimited...Use | to break the text.

Is that something you can work with?

**Steel Monkey** · 08-22-2007, 08:05 PM

I would use text to columns, there is an option to treat consecutive delimiters as one. If you select this it should not matter if there is more then one space.....

**dvandaniker** · 08-23-2007, 03:42 PM

Ron,

thanks for your solution. it worked great. I don't quite understand the formula but it works.

Thanks a million,
Derek

**dvandaniker** · 08-23-2007, 03:46 PM

Steel Monkey,

The consecutive delimiters option in "text to column" does not work for my problem because "consecutive" is taken to mean immediately consecutive, i.e. one character after another. The delimiters I could use are the spaces but these are at 2+ positions in the string that are not side by side.

anyways, thanks for your suggestion.

Derek

**Ron Coderre** · 08-23-2007, 03:54 PM

Originally Posted by dvandaniker

Ron,

thanks for your solution. it worked great. I don't quite understand the formula but it works.

Thanks a million,
Derek

Yes, it does look a little cryptic, doesn't it?

RE: =SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

Basically, this part: LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
takes the length of cell A1's displayed value and subtracts the length of the value without spaces. The difference is the number of spaces in that cell.

The wrap around SUBSTITUTE function replaces the last space (which is the count we calculated) with a pipe: |

So, if there were 3 spaces in A1 the formula partially resolves to this:
=SUBSTITUTE(A1," ","|",3)

I hope that helps.

GrandLake · 01-14-2009, 12:03 PM

I have reviewed the blog there you have the number last in a cell. How can I change the function to state if the number is first in the cell?

**EdMac** · 01-14-2009, 12:16 PM

GrandLake,

You should start a new thread with your problem rather than joining in an existing one.

GrandLake · 01-14-2009, 02:21 PM

Thank you for the prompt reply.
I have one cell that looks like
01 11 13 Work Covered by contract Documents
I wanted to have all the numbers in one column and the all the text in the next column.
I did a convertion on text to column that seperated into 8 columns. then I Concatenate the columns into one. this all worked out, but I was just wondering if there would have been an esier way to perform the task at hand. Thank you!!
Bea

**EdMac** · 01-14-2009, 03:04 PM

Talk to myself

**VBA Noob** · 01-14-2009, 03:05 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Old Thread closed. Please start you're own thread as already suggested

VBA Noob