OzGrid

How to extract characters

< Back to Search results

 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.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to extract multiple emails separated with semicolon and brackets
How to use SUMIF to extract attendance details from a register
How to extract letters from the string
How to create a custom function to extract integers from a simple 11 character string
How to extract information from a spreadsheet
How to use VBA code to extract rows of data meeting criteria

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)