Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to separate text and numbers
Hi, I have a spreadsheet with many rows of data containing a combination of
letters and numbers. I would like to be able to use formulas to separate the text from the numbers and end up with the letters in one cell and the numbers in another, so that the following: AB1 A20 TMH255 Would become: AB 1 A 20 TMH 255 Thank you in advance for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to separate text and numbers
"Chartreuse" wrote in message
... Hi, I have a spreadsheet with many rows of data containing a combination of letters and numbers. I would like to be able to use formulas to separate the text from the numbers and end up with the letters in one cell and the numbers in another, so that the following: AB1 A20 TMH255 Would become: AB 1 A 20 TMH 255 Thank you in advance for any help. Look here for a formula that will find the position of the first digit (0-9) in a string: http://www.cpearson.com/excel/stringformulas.aspx You could use this (say in B1) for data in A1, and then use these formulas to get the letters and numbers respectively: =LEFT(A1,LEN(A1)-B1) =RIGHT(A1,LEN(A1)-B1+1) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to separate text and numbers
Hi, Stephen, thank you very much for your response. I clicked on the link
and got the formula you mentioned and pasted it in along with the two formulas you gave me. The "RIGHT" formula worked great, but I didn't quite get the correct result using the "LEFT" formula. Here's the result I got: 1 A 20 TM 255 D1 100 XYZ 1000 I've tried changing the LEFT formula in various ways but I haven't hit upon the correct syntax yet. Do you know how I might be able to change it to make it work? Thanks in advance. "Stephen" wrote: "Chartreuse" wrote in message ... Hi, I have a spreadsheet with many rows of data containing a combination of letters and numbers. I would like to be able to use formulas to separate the text from the numbers and end up with the letters in one cell and the numbers in another, so that the following: AB1 A20 TMH255 Would become: AB 1 A 20 TMH 255 Thank you in advance for any help. Look here for a formula that will find the position of the first digit (0-9) in a string: http://www.cpearson.com/excel/stringformulas.aspx You could use this (say in B1) for data in A1, and then use these formulas to get the letters and numbers respectively: =LEFT(A1,LEN(A1)-B1) =RIGHT(A1,LEN(A1)-B1+1) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to separate text and numbers
Hi, actually, I think I was able to get it. I changed the formula in column
C to: =LEFT(A1,LEN(A1)-LEN(D1)) I have the result of the RIGHT formula in column D so subtracting the LEN of that seems to leave me with only the letters in column C. Thank you very much for your help! :) "Stephen" wrote: "Chartreuse" wrote in message ... Hi, I have a spreadsheet with many rows of data containing a combination of letters and numbers. I would like to be able to use formulas to separate the text from the numbers and end up with the letters in one cell and the numbers in another, so that the following: AB1 A20 TMH255 Would become: AB 1 A 20 TMH 255 Thank you in advance for any help. Look here for a formula that will find the position of the first digit (0-9) in a string: http://www.cpearson.com/excel/stringformulas.aspx You could use this (say in B1) for data in A1, and then use these formulas to get the letters and numbers respectively: =LEFT(A1,LEN(A1)-B1) =RIGHT(A1,LEN(A1)-B1+1) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to separate text and numbers
My apologies - I posted completely the wrong formula! Try this:
=LEFT(A1,B1-1) "Chartreuse" wrote in message ... Hi, Stephen, thank you very much for your response. I clicked on the link and got the formula you mentioned and pasted it in along with the two formulas you gave me. The "RIGHT" formula worked great, but I didn't quite get the correct result using the "LEFT" formula. Here's the result I got: 1 A 20 TM 255 D1 100 XYZ 1000 I've tried changing the LEFT formula in various ways but I haven't hit upon the correct syntax yet. Do you know how I might be able to change it to make it work? Thanks in advance. "Stephen" wrote: "Chartreuse" wrote in message ... Hi, I have a spreadsheet with many rows of data containing a combination of letters and numbers. I would like to be able to use formulas to separate the text from the numbers and end up with the letters in one cell and the numbers in another, so that the following: AB1 A20 TMH255 Would become: AB 1 A 20 TMH 255 Thank you in advance for any help. Look here for a formula that will find the position of the first digit (0-9) in a string: http://www.cpearson.com/excel/stringformulas.aspx You could use this (say in B1) for data in A1, and then use these formulas to get the letters and numbers respectively: =LEFT(A1,LEN(A1)-B1) =RIGHT(A1,LEN(A1)-B1+1) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to separate text and numbers
Hi, Stephen, I'm sorry to respond so late. I just wanted to say thank you
for the new LEFT formula. :) I tried it and it works great. :) Thanks for all your help! "Stephen" wrote: My apologies - I posted completely the wrong formula! Try this: =LEFT(A1,B1-1) "Chartreuse" wrote in message ... Hi, Stephen, thank you very much for your response. I clicked on the link and got the formula you mentioned and pasted it in along with the two formulas you gave me. The "RIGHT" formula worked great, but I didn't quite get the correct result using the "LEFT" formula. Here's the result I got: 1 A 20 TM 255 D1 100 XYZ 1000 I've tried changing the LEFT formula in various ways but I haven't hit upon the correct syntax yet. Do you know how I might be able to change it to make it work? Thanks in advance. "Stephen" wrote: "Chartreuse" wrote in message ... Hi, I have a spreadsheet with many rows of data containing a combination of letters and numbers. I would like to be able to use formulas to separate the text from the numbers and end up with the letters in one cell and the numbers in another, so that the following: AB1 A20 TMH255 Would become: AB 1 A 20 TMH 255 Thank you in advance for any help. Look here for a formula that will find the position of the first digit (0-9) in a string: http://www.cpearson.com/excel/stringformulas.aspx You could use this (say in B1) for data in A1, and then use these formulas to get the letters and numbers respectively: =LEFT(A1,LEN(A1)-B1) =RIGHT(A1,LEN(A1)-B1+1) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to separate text and numbers
Thanks for the help! I knew I should be able to parse items and amounts from
scanned receipts, but I didn't know exactly how. When I saw the LEFT program error that I also got, I knew I was on the right track! :) Took some tweaking of course for my particular use, but I had fun. "Chartreuse" wrote: Hi, actually, I think I was able to get it. I changed the formula in column C to: =LEFT(A1,LEN(A1)-LEN(D1)) I have the result of the RIGHT formula in column D so subtracting the LEN of that seems to leave me with only the letters in column C. Thank you very much for your help! :) "Stephen" wrote: "Chartreuse" wrote in message ... Hi, I have a spreadsheet with many rows of data containing a combination of letters and numbers. I would like to be able to use formulas to separate the text from the numbers and end up with the letters in one cell and the numbers in another, so that the following: AB1 A20 TMH255 Would become: AB 1 A 20 TMH 255 Thank you in advance for any help. Look here for a formula that will find the position of the first digit (0-9) in a string: http://www.cpearson.com/excel/stringformulas.aspx You could use this (say in B1) for data in A1, and then use these formulas to get the letters and numbers respectively: =LEFT(A1,LEN(A1)-B1) =RIGHT(A1,LEN(A1)-B1+1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i separate numbers and text in a cell? | Excel Discussion (Misc queries) | |||
Ascii iinput file - separate text from Numbers | Excel Worksheet Functions | |||
Converting Text String to Separate Numbers | Excel Discussion (Misc queries) | |||
How to separate numbers from text?? | Excel Discussion (Misc queries) | |||
Formula to separate text in cell | Excel Worksheet Functions |