• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Amounts (with currencies) from raw data text

M

mfpireas

New Member
Extract Amounts (with currencies) from raw data text
Hello everybody,
My name is Michail, I am from Greece and I found this Forum very interesting!!
I ‘m starting my first steps in Excel and I don’t have too much of experience …
I have the following problem (from the job) and I could use a little help (it is very difficult for me)
(My Excel version is MS-Excel 2007)

I have a payment Excel sheet, with thousands of lines
Every line (is one cell) has row payments data from different mainframes and looks like the following:

Lhfskldhflshlshdflsd 234 234 sdljflsdjfkl 345.000,56 EUR lshflhshlfsld 2342 sdffgsd
Fsdlflsdf -384823 jsdlfsdlfjsdlfjldfjsldjf 43 50.000,56 EUR fsdgdfgdsfdg 23432
Jlflsdjflsjdfld 569,00 USD fjsldjflslfhsdlhdlshg 3-448 jlfsdkljfklsd
Etc.

Is there a way (UDF or Macro) to extract from every line(cell) (and put them to the next cell to the right) from these raw data only the amounts with the Currencies. I could really need some help here…
Thank you very much in advance for your time and your efforts !

(I'm starting to be fascinated from excel world!!!

PS. (most of the times the amounts mentioned before are the biggest number in every line!)
PS2.(most of the times the currencies are USD or EUR)
PS3.(I have put my questions also in one other European Forum, but with no success)
 
Select the Column
Goto the Data, Text to Columns Tab
Use Delimited data
Choose a space as the delimiter
 
Hi Hui,
Thank you for the tip and your prompt reply.
But how can I pick my amounts??
(one it would be at A5 and its currency at A6, the next at B12 and its currency at B13
The next one at C8 and its currency at C9 and so on…..)
And of course in between other random numbers(not my amounts) delimited from a space….
How shall I know from thousand of lines in what cells my amounts and their currencies are?

Thanx in advance anyway ...
 
Hi,

Could mention the output from these three lines. i.e. the actual values you expect from these three lines
 
Hello Sathish,
A satisfied output (if you take my example) had to be something like the following
C1 D1
345.000,56 EUR
50.000,56 EUR
569,00 USD
 
Could you pls upload the sample file with sample answers / solution required in real scenario to help u correctly. Thank You!!
 
Hi vrunda,
I can not upload a test file cause the usb's in the company do not work, and i have also no email posibility to outside of the company, but my test proposal comes to very close to my problem
Thank also very much for trying helping me.....
 
Hello Michail,

If you are still looking for the answer, here is one way.

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,MIN(SEARCH({"EUR","USD"},A1))+3))," ",REPT(" ",20)),40))

As Hui said, if you have more currencies, then will require a twist.
 
You must log in or register to reply here.
Back
Top