# OzGrid

How to extract characters

Category: [Excel]  Demo Available

### How to extract characters

Requirement:

The user is trying to separate string into its components; please see below:

19303 AS,58

The user wants to split the above into different columns starting with AS, so my end result is:

Col 1 Col2
AS 58

Sometimes the data is as follows:

19303 AS

If there is nothing past the last space, then the column should read a zero as in:

Col 1 Col 2
AS 0

Other times the data is

19303 AS, LT

Any time it is a number, Excel needs to be able to convert the number stored as text into a number so that the user can use either vlookup or index/match

Solution:

The example shows that if anything follows the initial 5 digit number then it will be just 2 character text or a 2 digit number (or both separated by a comma). The example does not say if it would always be just 2 characters or digits, so the below formulas will allow for any number of characters or digits.

Not highly elegant formulas, but they work!!

in C5 copied down

=IFERROR(VALUE(IF(ISERROR(FIND(" ",\$B5)),0,IF(ISERROR(FIND(",",\$B5)),MID(\$B5,FIND(" ",\$B5)+1,LEN(\$B5)-FIND(" ",\$B5)),MID(\$B5,FIND(" ",\$B5)+1,FIND(",",\$B5)-FIND(" ",\$B5)-1)))),IF(ISERROR(FIND(" ",\$B5)),0,IF(ISERROR(FIND(",",\$B5)),MID(\$B5,FIND(" ",\$B5)+1,LEN(\$B5)-FIND(" ",\$B5)),MID(\$B5,FIND(" ",\$B5)+1,FIND(",",\$B5)-FIND(" ",\$B5)-1))))

In D5 copied down

=IFERROR(VALUE(IF(ISERROR(FIND(",",\$B5)),0,TRIM(MID(\$B5,FIND(",",\$B5)+1,LEN(\$B5)-FIND(",",\$B5))))),IF(ISERROR(FIND(",",\$B5)),0,TRIM(MID(\$B5,FIND(",",\$B5)+1,LEN(\$B5)-FIND(",",\$B5)))))

Obtained from the OzGrid Help Forum.

Solution provided by KjBox. 