Parsing into three pieces

[Viewson]

Hi,

I am new to vba and am stuck on how I get the below split into three columns?

A1=102341,80USD0,00

A2= 102341.80 (will also change the comma to a decimal afterwards)
A3=USD
A4=0,00 (will be deleted afterwards)

Thanks

[DonkeyOte]

Do you need VBA ?

Please Login or Register  to view this content.

above assumes

a) no leading zeroes in first number

b) first number always has 2 decimals (denoted by comma delimiter)

c) first number is followed by 3 letter currency code

all can be adapted as necessary.

[Viewson]

Do you need VBA ?

Yes, I do need VBA. I was able to get it to work in a normal excel sheet, but not in visual basic. There are actually multiple lines of data which require the same formatting and is currently in column K of my worksheet.

Here is a sample of additional lines:
102341,80USD0,00
31686,37USD0,00
42541,81USD0,00
89334,48USD0,00
92535,84USD0,00

The script I was attempting to use was:

Please Login or Register  to view this content.

What I need is for the currency "USD" to go into column I and the number (ex.102,341.80) to remain in column K.

[DonkeyOte]

Per Forum Rules - please always use CODE tags when posting VBA.

Numerous approaches - no doubt plenty more efficient than the below but I'm rushing off I'm afraid so not much thought has gone into the below I'm afraid

Please Login or Register  to view this content.

change Sheet reference as per your requirements

[Viewson]

Hi,

I was able to get the below to work perfectly, however just as you mentioned above one of the assumptions came to light today.

43956,USD0,00

This value has no decimals listed as they are .00. Is it possible to account for this in the parsing?

Per Forum Rules - please always use CODE tags when posting VBA.

Numerous approaches - no doubt plenty more efficient than the below but I'm rushing off I'm afraid so not much thought has gone into the below I'm afraid

Please Login or Register  to view this content.

change Sheet reference as per your requirements

[DonkeyOte]

Is the trailing value always 0,00 ? (and/or always has 2 decimals)

[DonkeyOte]

Try:

Please Login or Register  to view this content.

[Leith Ross]

Hello Viewson,

Here is another method to accomplish the task using Regular Expressions.

Please Login or Register  to view this content.

[Viewson]

Yes, the trailing value is alway 0,00.

Thanks, the new code did work.

[Viewson]

Hi Donkey,

Ran into a small issue today, when using the code below for only one line item on my spreadsheet I get a runtime error 13, type mismatch. Is there a way to use it when there is only one line item and then multiple lines.

Today's online line:
165751,39USD0,00

Pervious Multiple lines:
102341,80USD0,00
31686,37USD0,00
42541,81USD0,00
89334,48USD0,00
92535,84USD0,00
583075,02USD0,00
91665,89USD0,00
109809,74USD0,00
459433,87USD0,00
83198,76USD0,00
386154,16GBP0,00

Please Login or Register  to view this content.

[DonkeyOte]

Aplogies for belated response - I have not been online.

You could try modifying vStr line to:

Please Login or Register  to view this content.