How to extract characters

< Back to Search results

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.