ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   delimit using a formula (https://www.excelbanter.com/excel-discussion-misc-queries/258035-delimit-using-formula.html)

laandmc

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

Paul C

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


laandmc

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



All times are GMT +1. The time now is 04:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com