Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimit using a formula
I am wanting to delimit a cell with both text and numbers in to separate out
the numbers which have common characters that delimit them from the rest of the text. I can do this using text to columns but I want to know whether I can do this by using a formula instead.##e.g. A 1 text (2/3) more text 2 text (3/4) more text I want to get the results: A B 1 2 3 2 3 4 using the first delimiter "(", then "/" then ")" is there a way to write this in a formula to split up the numbers from the text? Cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimit using a formula
for the first number use this
=MID(A1,FIND("(",A1)+1,FIND("/",A1)-FIND("(",A1)-1) for the second number use this =MID(A1,FIND("/",A1)+1,FIND(")",A1)-FIND("/",A1)-1) Both of these work regardless of how long the numbers are (so (20/30) would be retured as 20 and 30). This does assume no extra spaces within the (1/2) expression -- If this helps, please remember to click yes. "laandmc" wrote: I am wanting to delimit a cell with both text and numbers in to separate out the numbers which have common characters that delimit them from the rest of the text. I can do this using text to columns but I want to know whether I can do this by using a formula instead.##e.g. A 1 text (2/3) more text 2 text (3/4) more text I want to get the results: A B 1 2 3 2 3 4 using the first delimiter "(", then "/" then ")" is there a way to write this in a formula to split up the numbers from the text? Cheers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
delimit using a formula
You are a legend thanks very much!
"Paul C" wrote: for the first number use this =MID(A1,FIND("(",A1)+1,FIND("/",A1)-FIND("(",A1)-1) for the second number use this =MID(A1,FIND("/",A1)+1,FIND(")",A1)-FIND("/",A1)-1) Both of these work regardless of how long the numbers are (so (20/30) would be retured as 20 and 30). This does assume no extra spaces within the (1/2) expression -- If this helps, please remember to click yes. "laandmc" wrote: I am wanting to delimit a cell with both text and numbers in to separate out the numbers which have common characters that delimit them from the rest of the text. I can do this using text to columns but I want to know whether I can do this by using a formula instead.##e.g. A 1 text (2/3) more text 2 text (3/4) more text I want to get the results: A B 1 2 3 2 3 4 using the first delimiter "(", then "/" then ")" is there a way to write this in a formula to split up the numbers from the text? Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Imported Data, How to delimit correctly | New Users to Excel |