How to split Number from a given text. |? Column A = Amount is $ 454.35 only. (given) | Column B = 454.35 (required)

as first2 words are same, use Text to Colums function

Step1: Text to Colums give you following result

Step2:  Text to column by space

just delet column1 &3 , No need to do complex formulas

I hope its help...

Thanks

Data in A1: Amount is $454.35 only

Formula in B1: =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$30),1)),0),(COUNT(1*MID(A1,ROW($1:$30),1))+1))

IMPORTANT!! You MUST enter these formulas as arrays by pressing CTRL+SHIFT+ENTER.

NOTE: You may need to increase the '30' in ROW($1:$30) if you are expecting longer inputs.

Result in B1:454.35

The key to distributing name components when you use text functions is the position of each character within a text string. The positions of the spaces within the text string are important because they indicate the beginning or end of name components in a string.

For example, in a cell that contains only a first and last name, the last name begins after the first instance of a space. Some names in your list may contain a middle name, in which case, the last name begins after the second instance of a space.

u can use  TEXT to COLUMN tab but  one more easy way is.

1     Copy above data to Column A and Column B

2Select Column B and Press CTRL +H

3Add "| Amount is " in FIND WHAT bar.

4Leave REPLACE WITH bar blank.

5In cell C1 of column C use the followin function

=LEFT(B1,SEARCH(" only",B1)-1)

yoou will find the  values like $454.35

if you want to remove $ signe thne use CTRL+H by selecting Column as mentioned above.

=mid(a1,11,3)

=mid(text,start,num)

format the cells (cntrl+1)

select currency and

select none for Symbol option

=MID(A2,12,LEN(A2)-17)

Use the Pipe de-limiter in excel to convert text into columns, pipe is the one on the black slash button of a keyboard.

Let say column "A" say Syntax "Amount is $______ only."

i will use followin formula and then simple drag it

Column B ==>    =LEN(A3)                    

        Providing the length of text in column A

 Column C==>     =SEARCH("$",A3)+1

        Providing the point after $

Column D==>   =SEARCH("only.",A3)

          Proving the point from where "only." starts

Column E==>   =MID(A3,C3,(D3-C3))

          Will start cutting from the point after $ till the length where only starts  :)

Change the target cell format from currency to number.