Retrieve partial number from cells

[loisluther]

Hi, I am new to this forum, and VERY new to the excel formula.
I am having trouble at work to retrieve partial number from cells.
Here is the source data in Column A:

A
S$100.09 @ 0.587682
USD 8859 @ 0.593472
EURO5,332.32@1.6633

(sometimes there's no space, sometimes there's no comma, but "@" will always be there to separate the amount and rate)

Below are what I would like to achieve in Column B to D .

B
SGD
USD
EUR

C
100.09
8859
5332.32

D
0.587682
0.593472
1.6633

It would really be appreciated if someone could help me to sort this out.
I tried to google but I really have no clue with formulae like SUMPRODUCT, INDEX, LEN, etc...
Really thanks!

[azumi]

Need clarify for currencies, when you have :

S$100.09 @ 0.587682

Why the results is :

SGD

Thanks

[azumi]

Put in Column B and copied down and cross :

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE($A1,"@",""),","," "))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(SUBSTITUTE($A1,"@",""),","," "))))),(COLUMNS(A1:$A$1)-1)*LEN($A1)+1,LEN(TRIM(SUBSTITUTE(SUBSTITUTE($A1,"@",""),","," ")))))

Hope it works

[loisluther]

Hi azumi, thanks for the speed response!!
for SGD or S$ it isn't that important, my apology.
But for the currency amount, I will need a column dedicated for just number only, and another column for just the exchange rate.
Thanks again in advance!!

[protonLeah]

Helper column (B1):

Please Login or Register  to view this content.

Column C:

Please Login or Register  to view this content.

column D:

Please Login or Register  to view this content.

column E:

Please Login or Register  to view this content.

[Vikas_Gautam]

I think this will do for many of the possible combinations..
For Currency Name

Please Login or Register  to view this content.

For Currency Amount

Please Login or Register  to view this content.

For Rate

Please Login or Register  to view this content.

Check the attached file..

[AlKey]

try this formula in B1 and pull formula to the right and then down

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(REPLACE($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&1234567890)),," "),"@"," "))," ",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

Row\Col	A	B	C	D
1	S$100.09 @ 0.587682	S$	100.09	0.587682
2	USD 8859 @ 0.593472	USD	8859	0.593472
3	EURO5,332.32@1.6633	EURO	5,332.32	1.6633

[azumi]

For Column C (Number Currencies):
=MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,"$"," "),","," "),FIND("@",SUBSTITUTE(SUBSTITUTE(A1,"$"," "),","," "))-1),FIND(" ",SUBSTITUTE(SUBSTITUTE(A1,"$"," "),","," "))+1,LEN(SUBSTITUTE(SUBSTITUTE(A1,"$"," "),","," ")))

For Exchange Rate:
=RIGHT(A1,LEN(A1)-SEARCH("@",A1))

[loisluther]

try this formula in B1 and pull formula to the right and then down

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(REPLACE($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},$A1&1234567890)),," "),"@"," "))," ",REPT(" ",255)),(COLUMNS($A:A)-1)*255+1,255))

Row\Col	A	B	C	D
1	S$100.09 @ 0.587682	S$	100.09	0.587682
2	USD 8859 @ 0.593472	USD	8859	0.593472
3	EURO5,332.32@1.6633	EURO	5,332.32	1.6633

Hi Alkey, this works like dark magic! But the results wouldn't be recognized as numbers.

[loisluther]

Thanks Vikas! The "Currency Amount" assumes there'd be a space after the currency name, but that isn't always the case. So when the original data says "SGD340" the formula returned only "40".

[loisluther]

Hey thank you all for your help!
To Alkey,
I added =Value() to your formula and now it worked the way I want. I still don't have a clue how one formula would get me all three parts I wanted Anyway THANKS!

[AlKey]

Thank you for the feedback!
Sorry, did know that you need those "numbers" converted to real numbers.

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).