Extract Number from Text String in a cell

[marilyn123]

Hi, I have consulted the numerous sources on this topic and can't seem to find a formula that will work for me. I have Excel 2010.
I have a row of colunms full of text cells such as the below. I want the formula to be able to extract just the number in an adjacent cell i.e. below. Currently we are using text to columns separating by space which is a good solution but would like to find something more exact to reduce time manually fixing lines if they don't have space between them.

I would prefer if the formula could read positive or negative but if that is not possible I have a workaround for that. Also, I can do basic vba coding so I would accept that as an answer but would prefer an excel formula.

CKSI MHJ to receive 8,456.12 EUR
CKSI MSUI to pay 934.56 DKK
CKSI MNU PAYS (1,456.84) SEK

Formula would produce
8,456.12
934.56
(1,456.84)

Thanks a million!

 

[sheetspread]

Excel 2010
	A	B
1	CKSI MHJ to receive 8,456.12 EUR	8,456.12
2	CKSI MSUI to pay 934.56 DKK	934.56
3	CKSI MNU PAYS (1,456.84) SEK	(1,456.84)
Sheet19

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200))," ",REPT(" ",100)),100))

 

[wideboydixon]

Doesn't extract the parentheses but this will work if there's only one number in the string:

Book1
	A	B
1	CKSI MHJ to receive 8,456.12 EUR	8,456.12
2	CKSI MSUI to pay 934.56 DKK	934.56
3	CKSI MNU PAYS (1,456.84) SEK	1,456.84
Sheet1

Cell Formulas
Range	Formula
B1	{=MID($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789")),MAX(IF(ISNUMBER(VALUE(MID($A1,ROW(INDIRECT("1:" & LEN($A1))),1))),ROW(INDIRECT("1:" & LEN($A1)))))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"))+1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[marilyn123]

This formula worked but included the words pay or receive about 20% of the time. I cant figure out why. Will let you know if I do

 

[marilyn123]

This formula worked but included the words pay or receive about 20% of the time. I cant figure out why. Will let you know if I do

 

[marilyn123]

Sorry I am having trouble figuring out how to reply to individual responses!

=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200))," ",REPT(" ",100)),100))

This formula worked about 80% of the time, included words pay or receive about 20% of them.

{=MID($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789")),MAX(IF(ISNUMBER(VALUE(MID($A1,ROW(INDIRECT("1:" & LEN($A1))),1))),ROW(INDIRECT("1:" & LEN($A1)))))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"))+1)}

This formula worked 100% of the time.

Sorry for any confusion!

 

[wideboydixon]

Sorry I am having trouble figuring out how to reply to individual responses!

=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200))," ",REPT(" ",100)),100))

This formula worked about 80% of the time, included words pay or receive about 20% of them.

{=MID($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789")),MAX(IF(ISNUMBER(VALUE(MID($A1,ROW(INDIRECT("1:" & LEN($A1))),1))),ROW(INDIRECT("1:" & LEN($A1)))))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&"0123456789"))+1)}

This formula worked 100% of the time.

Sorry for any confusion!

The first formula just extracts the text between the last and next-to-last space which is why it's not necessarily reliable. The second one is actually looking for a number in the string. Glad it's working for you.

WBD

 

[sheetspread]

This formula worked about 80% of the time, included words pay or receive about 20% of them.

Were there spaces after those words? Can you show a sample?

2 more substitutes can clear them.

 

[Sunny Kow]

You can give this UDF a try

Function SKDecimalNumbers(ByVal InString) As String
   Dim x As Integer
   For x = 1 To Len(InString)
     If Mid(InString, x, 1) Like "[!0-9.,() ]" Then Mid(InString, x, 1) = Chr$(1)
   Next
   SKDecimalNumbers = Trim(Replace(InString, Chr$(1), ""))
End Function

 

[melvinkoshy]

Select a blank cell that is adjacent to the list you want to extract number only, and type this formula

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

(A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula"


It's not necessary to press Shift + Ctrl + Enter. Press only ENTER (sumproduct know to work with array)

Reference :https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html