Extract numeric value from the text string

**sekharks** · 10-19-2021, 12:16 AM

Dear Experts,

I have a file containing text string of 500 rows. Out of which, I have to extract only the numeric value that appears after a particular text.

Ex:

CMP00000000381181704AOE1975372 TRANSFER TO 33164290629 - I want to extract only numeric value appearing after "TRANSFER TO".

I have used FIND function and tried to extract the number. Only concern for me was all the text strings are not constant. They have other values before and after the numeric value.

Request you to please help me find a solution.

Attaching a file with sample text string for your reference.

**FDibbins** · 10-19-2021, 12:39 AM

A little bit long winded, but try...
=IFERROR(LEFT(MID(B2,SEARCH("to",B2)+3,99),FIND("/",MID(B2,SEARCH("to",B2)+3,99))-2),MID(B2,SEARCH("to",B2)+3,99))

**bebo021999** · 10-19-2021, 12:40 AM

Using MID to extract multi values, starts from FIND+12 (12 is lenght of "TRANSFER TO"), with 1,2,3,...,15 digits (3;33;331;3316;..,33164290629), then get the max (with AGGREGATE(14,6,...,1))

=AGGREGATE(14,6,--MID(B2,FIND("TRANSFER TO",B2)+12,ROW($1:$15)),1)

**sekharks** · 10-19-2021, 01:30 AM

Thank you both FDibbins and bebo021999 for your valuable functions.

Since, both the formulas have multiple functions combined have to practice and try to memorise them for future use.

**FDibbins** · 10-20-2021, 12:19 AM

Happy to help and thanks for the feedback

Extract numeric value from the text string

LinkBack

Thread Tools

Rate This Thread

Display

Extract numeric value from the text string

Re: Extract numeric value from the text string

Re: Extract numeric value from the text string

Re: Extract numeric value from the text string

Re: Extract numeric value from the text string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Extract numeric values from the string

Extract Numeric from a String

[SOLVED] Extract numeric part of string

[SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string

Extract numeric value from a string of unknowns

Extract numeric and char from string!

Bookmarks

Bookmarks

Posting Permissions