Extract Numbers from Text

[hunscot]

Extract Numbers from Text

Looking to find the best way to auto fill a cell with number data
extracted from file text.

Scenario:

CELL A1: 01_BED_JAZZ_TRIO_120BPM.wav

CELL B1: 02_BED_BIGBAND_FULL_96BPM.wav

I want to extract tempo numbers 120 from Cell A1 and 96 from B1

I can use Text to Col but I am trying to get this as a one step
solution.

The underscore in the 2 digit tempo is also problematic. My MID
attempts are not sophisticated enough. Power user advice most welcome!

 

[JE McGimpsey]

CELL A1: 01_BED_JAZZ_TRIO_120BPM.wav

CELL B1: 02_BED_BIGBAND_FULL_96BPM.wav

I want to extract tempo numbers 120 from Cell A1 and 96 from B1

I can use Text to Col but I am trying to get this as a one step
solution.

The underscore in the 2 digit tempo is also problematic. My MID
attempts are not sophisticated enough. Power user advice most welcome!

One way:

=MID(LEFT(A1, LEN(A1) - 7), FIND("^", SUBSTITUTE(A1, "_", "^",
LEN(A1) - LEN(SUBSTITUTE(A1, "_", "")))) + 1, 255)

If you want the values as numbers rather than text:

=--MID(LEFT(A1, LEN(A1) - 7), FIND("^", SUBSTITUTE(A1, "_", "^",
LEN(A1) - LEN(SUBSTITUTE(A1, "_", "")))) + 1, 255)

Note that 255 is just a large number: 3 would likely work as well.

 

[hunscot]

One way:

=MID(LEFT(A1, LEN(A1) - 7), FIND("^", SUBSTITUTE(A1, "_", "^",
LEN(A1) - LEN(SUBSTITUTE(A1, "_", "")))) + 1, 255)

If you want the values as numbers rather than text:

=--MID(LEFT(A1, LEN(A1) - 7), FIND("^", SUBSTITUTE(A1, "_", "^",
LEN(A1) - LEN(SUBSTITUTE(A1, "_", "")))) + 1, 255)

Note that 255 is just a large number: 3 would likely work as well.

JE,

Works well, many thanks!!!!