Excel - Extract 2 characters after specific text in string

Question

I'm hoping someone can help me with an Excel formula to extract two characters after specific text in a string.

Specifically, the cell contains the following text

Every 2 Hours Power:1 Sail:0 SWC:3 BCR:0 CJC:0 CU:0 CSC:0

Note that all cells are slightly different so I can't just use the text to columns delimiter. I need the formula to return the two characters after the specific text "Power:". From the example above, I want the cell to read "1" after the text "Power:".

I've tried various combinations of Right, Left, Len, Search, and am not having success. Thanks in advance!

Assuming that text is in A1: =MID(A1,SEARCH("Power:",A1)+6,2) — Rory
– Rory, Commented Dec 28, 2023 at 23:00
And add “*1” to @Rory answer so it becomes a number instead of text, could also use value() — Solar Mike
– Solar Mike, Commented Dec 28, 2023 at 23:02
Thank you Rory and Solar Mike! This worked to get the numbers when there was accidentally a space between the : and the #. I then also used Mayukh's solution below to extract when there was no space. Thank you!! — KRD
– KRD, Commented Dec 30, 2023 at 15:41

Mayukh Bhattacharya · Accepted Answer · 2023-12-29 05:21:00Z

1

Assuming there is no Excel Constraints as per the tags posted then one could use the following formula, which uses TEXTAFTER() & TEXTBEFORE()

=--TEXTBEFORE(TEXTAFTER(A1,"Power:")," ")

Or, Spill all at once,

=--TEXTBEFORE(TEXTAFTER(A1:A4,"Power:")," ")

Or, with TEXTSPLIT() & TEXTAFTER()

=--TEXTSPLIT(TEXTAFTER(A1:A4,"Power:")," ")

answered Dec 29, 2023 at 5:21

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

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

1 Comment

KRD Over a year ago

Thank you Mayukh! This works perfectly. I used your first solution here. Happy New Years

Harun24hr · Accepted Answer · 2023-12-29 02:15:19Z

1

If you are on Microsoft-365 then go with TEXTSPLIT().

=--TAKE(TEXTSPLIT(TEXTAFTER(A1,"Power:"),," "),1)

For other versions, I would suggest to use FILTERXML().

answered Dec 29, 2023 at 2:15

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

Collectives™ on Stack Overflow

Excel - Extract 2 characters after specific text in string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related