Extracting Text/Numbers from an AlphaNumeric String.

[e4excel]

Extracting Text/Numbers from an AlphaNumeric String.

Dear Forum,

I am in need of seperating Numbers and Alphabets joined by a Special Character..

Ex:

1> AAA/BBB


I want to get "AAA" as well as "BBB"


2> 123/456

I want to get "123" as well as "456"


3> 12/345

I want to get "12" as well as "345"

4> 123/45

I want to get "123" as well as "45"


I tried the below code but it failed

=RIGHT(A$1,LEN(A$1)-FIND("/"A$1,1))-LEFT(A$1,LEN(A$1)-FIND("/",A$1,1))

This failed in these two cases

3> 12/345

4> 123/45

Warm Regards
e4excel

[NBVC]

=Left(A1,FIND("/",A1)-1)

and

=MID(A1,FIND("/",A1)+1,100)

or you can use DATA|TEXT TO COLUMNS with / as delimiter...

[sweep]

Hi

Before the oblique,

=LEFT(A1,FIND("/",A1)-1)

After the oblique,

=RIGHT(A1,LEN(A1)-FIND("/",A1))

[e4excel]

HEy thanks everyone,


I thought that maybe something was possible using the SUBSTITUTE function but this was very easy..thanks a lot NBVC and sweep..

If there's any other way please feel free to ADD.

[e4excel]

Is there a way of confirming if there exists a special character such as "/" in a cell..

I have the cells in the Column Heading..

144/233, 89/144 and in the last column heading it becomes just 1 which does not have "/", so how do I address this issue?

[NBVC]

Try then:

=LEFT(A1&"/",FIND("/",A1&"/")-1)

and

=SUBSTITUTE(MID(A1&"/",FIND("/",A1&"/")+1,100),"/","")

[e4excel]

Hey thanks NBVC,

can u also please tell me how do icheck for the presence of the "/" in the cell? as I tried using ISTEXT() but it fails as by putting values such as 144/233 makes the cel into TEXT.

[NBVC]

=Isnumber(Find("/",A1))

[e4excel]

=Isnumber(Find("/",A1))

Thanks a lot...NBVC