LinkBack URL
About LinkBacks
I have a 2010 version of MS Excel. I have roughly 10000 cells that I need to separate into two columns from one cell. What's the best way to do this?
Here is an example of one cell "John Smith 888-8888".
Thank you in advance.
I have a 2010 version of MS Excel. I have roughly 10000 cells that I need to separate into two columns from one cell. What's the best way to do this?
Here is an example of one cell "John Smith 888-8888".
Thank you in advance.
Is your intent to have John Smith in one cell and 888-8888 in another or do you just want to eliminate the text or the number?
Option: Use TeXt to Columns with space as the delimiter.
Last edited by protonLeah; 01-12-2014 at 05:46 PM.
Ben Van Johnson
Sorry, I reread the original post and answered my own question.
If the numbers are always the same length...
A2 = John Smith 888-8888
To extract the name:
=LEFT(A2,LEN(A2)-9)
To extract the number:
=RIGHT(A2,8)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
The numbers and text vary. I tried to use the right function, but then it leaves the text and the numbers still in the same cell together.
I need both sets of data - the text and the numbers.
Last edited by watchoverme; 01-12-2014 at 04:01 AM.
provide some sample data
If the string is in A1 and the two parts are separated with one space, enter into B1:
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-2)
into C1:
=TRIM(SUBSTITUTE(A1,B1,""))
Sample Data as requested:
Ronald Klein 321-0166
Calvin Stephens 462-8016
Russell Summers 475-8820
Charlotte Stewart 468-2163
Trevor 329-1171
Jillian 235-3535
It is just data with names then telephones. The names vary in length and do have spaces in between some of them.
Damn AutoCorrect!!!
That should be "Text To Column" (not Test to Column).
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Haha. I know what you meant. Unforuntaely, I'm not able to use text to columns because I don't have any unique characters separating the text with the numbers except for the space, but I have many cells with multiple spaces.
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
Try this formula, the result is the text from last space to the end.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
I pasted the formula exactly as is to the right of my column and a FALSE displayed.Originally Posted by popipipo
Here my sample workbook
And some addition to your sample workbook by me
Would this be by chance what you are looking for? I separated the text from the telephone numbers having the text in one column and the telephone number in another column.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
YES! Genius. Thank you so much. Can you explain to me your formula in excel because I've never encountered TRIM and SUBSTITUTES.
Microsoft does it with F1Can you explain
These are the results I get based on your posted sample data using the formulas suggested in post #5:
Data Range
In message #17, who are you referring to for an explanation?
In case you are referring to my message #16
The first formula entered in the worksheet was in F1
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",75)),75))
Look at the formula from the inside outwards. The innermost function is SUBSTITUTE. SUBSTITUTE basically looks at the cell that you want to make substitutions in ( A1) the next argument is what you want to substitute and in this case it is the spaces in the text ( " " ). The following argument is what is going to be the replacement. In this case REPT (repeat) will insert 75 spaces for every space in A1.
RIGHT has two arguments - what cell and how many characters. The number in A1 has no spaces so is preceded by 75 spaces. So the RIGHT function is now looking at this long string of characters with 75 spaces between the different elements. The last element being the telephone number. The RIGHT function is just taking the last 75 characters which includes the telephone number (8 characters) and a bunch of spaces.
The TRIM function "trims" away the excess spaces from the ends of the text thus leaving just the telephone number.
The second formula entered is entered in E1
=TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",75)),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",75)),75)) ," "))
If you look at the formula, the BOLD part is the first formula inserted into this formula. The second SUBSTITUTE is there to substitute the spaces in A1 with 75 spaces each as in the first formula. The first SUBSTITUTE then takes this very long string and substitutes the bold part of the formula (the telephone number) with a space...the last " " in the formula.
The TRIM function now removes all excess spaces from the beginning and end of the string and leaves a single space between the remaining elements of the cell.
I hope that this confusing explanation isn't too confusing to be understood.
Last edited by newdoverman; 01-12-2014 at 05:55 PM.
Post #5, #8, #12, #16 Gives all the same result.using the formulas suggested in post #5:
The idea behind the formula is this (I think).
The TRIM function removes all leading and trailing spaces from a text string. It also reduces all double spaces to a single space.
Using the above information you replace every single space in a string to one that is 75 spaces or some big number.
Then you use the Right function to take the last 75 characters of this "space padded" string and do a TRIM on it.
I saw this trick a few years ago from DonkeyOte. It all revolves around knowing how TRIM works.
I hope this explanation lets you understand what I think is being done in formulas.
If you use the Evaluate Formula tool you can step through the formula and see it work..
http://www.officearticles.com/excel/...soft_excel.htm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks