أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.
| TEXT - | = |-Value | | Amount is $45 only. | = |45 | | Amount is $454.35 only. | = |454.35 | | Amount is $321.00 only. | = |321.00 | | Amount is $9935.20 only. | = |9935.20 | | Amount is $14253.92 only. | = |14253.92 | | Amount is $6.35 only. | = |6.35 |
as first2 words are same, use Text to Colums function
Step1: Text to Colums give you following result
Amount is $321.00 only. Amount is $9935.20 only. Amount is $14253.92 only. Amount is $6.35 only.
Step2: Text to column by space
$321.00 only. $9,935.20 only. $14,253.92 only. $6.35 only.
just delet column1 &3 , No need to do complex formulas
Amount is $321.00 only. Amount is $9,935.20 only. Amount is $14,253.92 only. Amount is $6.35 only.
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.