Excel Extraction, taking out three numeric characters in a string

Question

I am looking to find an excel formula or VB formula which would be able to extract specific data in excel.

For example we have the following in a cell: Time spent in script:333

The above will always remain the same, however the time will be different and can range from 1 to 999 so 3 numeric characters max. However.. In some cells there maybe changes after the 3 characters and may come out like so: Time spent in script:333Q1

So I need a formula which extracts 3 numeric characters after the semi colon but to ignore anything after the 3 characters.

You say semi colon which is ; I see : a colon

Scott Craner
– Scott Craner

2017-06-07 15:28:58 +00:00
Commented Jun 7, 2017 at 15:28 — Scott Craner
– Scott Craner, Commented Jun 7, 2017 at 15:28

Gowtham Shiva · Accepted Answer · 2017-06-07 15:57:34Z

2

Quite lengthy, but should help you,

=IF(ISNUMBER(VALUE(LEFT(MID(A1,FIND(":",A1)+1,99),3))),VALUE(LEFT(MID(A1,FIND(":",A1)+1,99),3)),IF(ISNUMBER(VALUE(LEFT(MID(A1,FIND(":",A1)+1,99),2))),VALUE(LEFT(MID(A1,FIND(":",A1)+1,99),2)),VALUE(LEFT(MID(A1,FIND(":",A1)+1,99),1))))

edited Jun 7, 2017 at 15:57

answered Jun 7, 2017 at 15:36

Gowtham Shiva

3,8852 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SQLeon Over a year ago

This works, however when I have: Time Spent in script:502Q1 it only brings back 50. However I need to pull through a maximum 3 values

Scott Craner Over a year ago

@SQLeon does your values actually have a space between the : and the first number?

dwirony · Accepted Answer · 2017-06-07 15:37:33Z

-1

A1 being the cell you're referencing...

=MID(A1,22,3)

EDIT: Now works for digits 1-999

=IF(LEN(A1) = 26, MID(A1,22,3), IF(LEN(A1) = 25, MID(A1,22,2), IF(LEN(A1) = 24, MID(A1,22,1), "NA")))

edited Jun 7, 2017 at 15:37

answered Jun 7, 2017 at 15:29

dwirony

5,4604 gold badges26 silver badges49 bronze badges

3 Comments

Gowtham Shiva Over a year ago

Does the number start always at position 22?

dwirony Over a year ago

According to OP it does. But I forgot to account for the number being 1-3 digits long - will have to fix.

dwirony Over a year ago

Eh, I misread the initial question... It looks like Gowtham has provided a viable solution for you :)

Collectives™ on Stack Overflow

Excel Extraction, taking out three numeric characters in a string

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related