Excel VBA - Split String

[mccrimmon]

Hi,

I have a string that can be passed through in various different formats:

"CR 123456 DB 654321 EUR"
" CR 123456 DB 654321 EUR "
"DR123456CR654321"
"DR 123456 CR 654321 GBP"
" DR 123456 CR654321 GBP "

etc etc

I am basically looking to pull out the 6 digit DR number and 6 digit CR number from the strings regardless of the passed in format.

So I would have something like GetCreditAccount = "123456" and GetDebitAccount = "654321"

Is anyone able to help?

Thanks

[Steffen Thomsen]

Hi,

You can use these functions

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mccrimmon]

Hi

Thanks for your response, however, that only works when the CR account is the first account.

Sometimes the string will be passed with the DR account first such as the below examples:

"DR123456CR654321"
"DR 123456 CR 654321 GBP"
" DR 123456 CR654321 GBP "

Any thoughts?

Thanks

[Steffen Thomsen]

These are closer.

I wonder, in your first post you have both DB and DR for the same thing, can the format be like this or is it an error?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mccrimmon]

Apologies.

They should all be DR and CR.
Just a typo on the example data.

[mccrimmon]

Thanks for the help.

Seems to work a treat.

[TMS]

An alternative, if more long winded version:

Please Login or Register  to view this content.

Regards, TMS

[mccrimmon]

A further question though and I'm not trying to over complicate things, I'm just making sure I add in as much control as I can to the function.

Is there anyway to test that each account is 6 numeric digits.

For example if I pass in a string of "DR12345CR654321" (the DR account missing a digit) then I would be looking for this to only show 12345 and then error due to only having 5 digits rather than 6.

The current process would make this DR account 12345C, which is incorrect.

Any suggestions from anyone is greatly appreciated.

[Kyle123]

or maybe even:

Please Login or Register  to view this content.