ابدأ بالتواصل مع الأشخاص وتبادل معارفك المهنية

أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.

متابعة

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

| 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 |

user-image
تم إضافة السؤال من قبل Ahmed Aziz , Software Development & Testing , Innernet, Inc.
تاريخ النشر: 2014/03/27
مستخدم محذوف‎
من قبل مستخدم محذوف‎

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

 

 

Mehaboob Abdul Kareem
من قبل Mehaboob Abdul Kareem , Project Administrator , Royal Gardens Contracting Co.

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

Omar Siddique
من قبل Omar Siddique , Senior Sales & Operations Manager , Talya Tourism LLC

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.

 

ALLAH DITTA
من قبل ALLAH DITTA , Accountant , Master Beverages & Food Limited

=mid(a1,11,3)

=mid(text,start,num)

Faiyazuddin Mohammed
من قبل Faiyazuddin Mohammed , Research Associate , Cognizant (UBS Service Centre India Pvt. Ltd)

format the cells (cntrl+1)

select currency and

select none for Symbol option

 

Ahmed Aziz
من قبل Ahmed Aziz , Software Development & Testing , Innernet, Inc.

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

Akhil Gupta
من قبل Akhil Gupta , customer experience and analytics manager , Dubai Properties Group

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

Zaheer Waheed
من قبل Zaheer Waheed , Senior Regional Accounting Manager - Poultry Sales , Almarai Company - Alyoum

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  :)

 

 

Muhammad Usman Tahir
من قبل Muhammad Usman Tahir , Assistant Manager ERP , Sapphire Fibers Limited

Change the target cell format from currency to number.

المزيد من الأسئلة المماثلة