Whirlpool

Hi Guys

I'm very new to Excel VBA and I would like some ideas on doing some string manipulation for a small excel macro i'm writing for work.

Basically I need to do this:

'1A25K2' transform to '1A025K02'

1) The numbers after the first alpha character needs to be 3 digits (if not already). If it is 2 digits, add a '0' in front.

2) The numbers after the 'K' needs to be a 2 digit number. If it only has one digit, add a '0' in front.

3) if there is no 'K' in the string, leave the string alone.

Regards

Alvin

Public Function convert(strText As String) As String
Dim strSplitK
Dim strSplitA

If InStr(strText, "A") > 0 And InStr(strText, "K") > 0 Then
strSplitK = Split(strText, "K")
strSplitA = Split(strSplitK(0), "A")

If IsNumeric(strSplitK(1)) And IsNumeric(strSplitA(1)) Then
convert = strSplitA(0) & "A" & Format(CInt(strSplitA(1)), "000") & "K" & Format(CInt(strSplitK(1)), "00")
Else
convert = strText
End If
Else
convert = strText
End If
End Function

Quick, dirty and nasty. Just the way all VBA should be. :)

Hi Paul

Thanks for the code! Um.. would it be too much trouble to ask for an explination on how it works?

Once again thanks heaps!!!!

Regards

Alvin

*grin*

The Split() function returns a zero-based array of strings, where the original string has been chopped up using the given character as a delimiter.

So, if we assume that the input string (strText) is '1A25K2', then

strSplitK = Split(strText, "K") returns an array of two strings, '1A25' and '2'

the second split uses the first string from the first split ('1A25'), split around the 'A' character, so:

strSplitA = Split(strSplitK(0), "A") returns an array of two strings, '1' and '25'

the business end of the function is this line:

convert = strSplitA(0) & "A" & Format(CInt(strSplitA(1)), "000") & "K" & Format(CInt(strSplitK(1)), "00")

which creates a new string, concatenating the first part of the second split, the second part of the second split (converted into an integer - CInt() - and forced to use a leading zero - Format()) and the second part of the first split processed the same way.

MSDN help articles which explain the various functions in a lot more detail:
Split, Format, and &, the string concatenation operator.

Hi Paul

Thanks for the explination =Þ. I'll look at the links and find out more info about those functions. Are you in the software development industry?

I'm an IT Administrator and this is my first (hopefully last) programming project for work.

Alvin

The macro is a fairly elegant solution. However, I note the following in the original spec:

1) The numbers after the first alpha character ...

The macro is assuming that this character is an "A". If this character may vary (eg. A, B, C, a, b, c, etc), the macro will require quite a bit more complexity.

Alvin writes...

would it be too much trouble to ask for an explination on how it works?

Another way to answer this question (and I am not sure if this is what Alvin meant) is to explain how to use it. Alvin did say he was new to programming.

Put that code in a module.

use the function =convert(a1) in your spreadsheet (assuming a1 is where your string data is ).

Hope I have not insulted anyones intelligence here.

myobman writes...

Another way to answer this question (and I am not sure if this is what Alvin meant) is to explain how to use it.

Good point, myobman.

Another way to use it, particularly if your data is coming from another source (such as a text file) is to run through a loop, at each iteration reading one entry, calling this conversion, then outputting the converted data.

To the OP - please advise if you need some help with either of these suggestions.

Alvin writes...

Are you in the software development industry?

I'm a VB6 programmer, yes.

SmartyFool writes...

The macro is assuming that this character is an "A".

An excellent point... *hides*

myobman writes...

Another way to answer this question (and I am not sure if this is what Alvin meant) is to explain how to use it.

Umm...yes, also an excellent point. Hey, I'm a programmer - if the code works, some other bugger can deal with interfacing to the users. :D

Hi GUys

I've tried out Paul's code a and figured out how to use it, and it works a treat! Thanks guys. Yes, I was just about to post about the "The numbers after the first alpha character..." thing. Was wondering, was there a function that returns the nth character in the string? with this example, all that would be needed is to return the second character which is the alpha character, store it in a variable, and then substitute that variable in the "InStr(strText, "A")" function or where ever it is needed. This will make it more dynamic??

What you guys think? I've tried looking for a function that does that (returning nth character), but couldn't find on in Google.. yet.

There is another requirement that is similar to my first programming problem, but I would like to tray and do it myself before posting for help. I think I can modify Paul's code to get it to work for the second requirement. It is to manipulate a string that is in another format (but similar, need to remove all spaces first). I'll post it here when I can get time to modify it.

Alvin

Alvin,

You might want to check out:

Left
Mid
Right

All are capable of returning the nth character from a string, the only difference being from where you start (the left, anywhere or the right respectively).

You can also substitute an integer value for the return of instr (or instrrev)