split stringer in letters and numbers

[iscar_marius]

Let's say I have this stringer: "abc123", how can I split this in "abc" and "123" ? the number of letters and numbers is not the same but all the time I have first letters and after that numbers. For example I can have this kind of stringers: aa1, ea23, ef5 etc.

[iscar_marius]

I found this code:

Please Login or Register  to view this content.

How can I modify it so the result will be a stringer "123" ?

[JeanRage]

Hi,

The first step is to determine the position of the first number in your text string

Please Login or Register  to view this content.

Then, if the functions Left() and Right() you will be able to split all your strings ...

HTH

[DonkeyOte]

JR, easiest to append the string with all integers, eg:

Letters

B8: =LEFT(A8;MIN(FIND({1\2\3\4\5\6\7\8\9\0};A8&1234567890))-1)

Numbers are thus

C8: =0+REPLACE(A8;1;LEN(B8);"")

Or

C8: =0+SUBSTITUTE(A8;B8;"")

(if you prefer the number as string remove the 0+ coercion)


edit: revised delimiters per OP locale

[iscar_marius]

Hi,

The first step is to determine the position of the first number in your text string

Please Login or Register  to view this content.

Then, if the functions Left() and Right() you will be able to split all your strings ...

HTH

Can I use this in vba? I will try to read more about this ... A8 is a range?

The code from my previous post is working but the results is 3 msgboxes: msgbox1is 1,msgbox2 is 2, msgbox3 is 3. Is not possible to unify this values ?

[iscar_marius]

I really need to do this with vba code. I need to combine this with other vba code that I have.
Thank you for your answer!

[DonkeyOte]

Is not possible to unify this values ?

Please elaborate regards the above - unify ?

[iscar_marius]

Please elaborate regards the above - unify ?

Using that code, the result will be all the numbers from the stringer separately. I need all of this values toghether in a stringer. unify = put toghether ( I don't know if unify is a english word ). In the above example, I need a new stringer "123".
Sorry for my bad english!

[DonkeyOte]

Simply remove the For Next loop which is presently iterating the resulting number char by char and return the variable as a whole, ie:

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

FWIW - you could avoid the Regular Expressions and use for ex.:

Please Login or Register  to view this content.

[iscar_marius]

This is exactly what I need. Thank you very much!