|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
08-22-2007, 08:33 PM
|
|||
|
|||
|
separate text and numbers
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 
|
|
#2
08-22-2007, 09:03 PM
|
||||
|
||||
|
separate text and numbers
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?
|
|
#3
08-22-2007, 09:05 PM
|
|||
|
|||
|
separate text and numbers
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.....
|
|
#4
08-23-2007, 04:42 PM
|
|||
|
|||
|
substitution works great
Ron,
thanks for your solution. it worked great. I don't quite understand the formula but it works. Thanks a million, Derek
|
|
#5
08-23-2007, 04:46 PM
|
|||
|
|||
|
consecutive delimiters
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
|
|
#6
08-23-2007, 04:54 PM
|
||||
|
||||
|
separate text and numbers
Quote:
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.
|
 |
| Bookmarks |
New topics in F1 Get the most out of Excel Formulas & Functions
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Linear Mode
