Extracting Text/Numbers from an AlphaNumeric String.

**e4excel** · 08-04-2010, 04:25 PM

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

Please Login or Register  to view this content.

This failed in these two cases

3> 12/345

4> 123/45

Warm Regards
e4excel

**NBVC** · 08-04-2010, 04:30 PM

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

and

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

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

**sweep** · 08-04-2010, 04:30 PM

Hi

Before the oblique,

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

After the oblique,

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

**e4excel** · 08-04-2010, 04:36 PM

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** · 08-04-2010, 04:45 PM

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** · 08-04-2010, 04:51 PM

Try then:

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

and

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

**e4excel** · 08-04-2010, 05:11 PM

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.