How do you separate text in excel vba by "CamelCase" and numbers

Question

I'm trying to make a molecular composition calculator but i can seem to separate a formula by case and numbers into different cells.

Is it possible to do this in excel?

Eg:

Cl2H0   ----> Cl  |  2  |   H |    0

score 1 · Answer 1 · answered Feb 09 '17 at 22:59

A bit crude but you could write a parsing function like this that returns an array:

Public Function parseChem(str As String) As Variant()
  'should error-check first that entire string is correct

  Dim retArr() As Variant
  Dim i As Long, numBlocks As Long
  Dim currentChar As String, currentElement As String, typeOfChar As String
  Dim digitChain As Boolean

  For i = 1 To Len(str)
        currentChar = Mid(str, i, 1)
        typeOfChar = charType(currentChar)
        Select Case typeOfChar
              Case Is = "upperCase"
                    If currentElement <> "" Then
                          'possibly cast numbers to longs here, and at the end...
                          retArr(numBlocks) = currentElement
                    End If
                    numBlocks = numBlocks + 1
                    ReDim Preserve retArr(1 To numBlocks)
                    currentElement = currentChar
                    digitChain = False
              Case Is = "lowerCase"
                    currentElement = currentElement & currentChar
              Case Is = "digit"
                    If digitChain Then
                          currentElement = currentElement & currentChar
                    Else
                          'new digit block
                          retArr(numBlocks) = currentElement
                          numBlocks = numBlocks + 1
                          ReDim Preserve retArr(1 To numBlocks)
                          digitChain = True
                          currentElement = currentChar
                    End If
              Case Else
                    'do something to flag error
        End Select
  Next i

  retArr(numBlocks) = currentElement

  parseChem = retArr
End Function

Private Function charType(str As String) As String
  Dim ascii As Long
  ascii = Asc(str)
  If ascii >= 65 And ascii <= 90 Then
        charType = "upperCase"
        Exit Function
  Else
        If ascii >= 97 And ascii <= 122 Then
              charType = "lowerCase"
              Exit Function
        Else
              If ascii >= 48 And ascii <= 57 Then
                    charType = "digit"
                    Exit Function
              End If
        End If
  End If
End Function

score 0 · Answer 2 · answered Feb 09 '17 at 14:16

0

If you are familiar with VBA then you could write a function which reads in the cell value (e.g. Cl2H0) and then a For Loop that splits the string into seperate values. You would then write these seperated values (Cl, 2, H and 0) back to indivisual columns on the excel sheet.

One way of doing this would be to use the Asc() function in a loop which will give you the Ascii number corresponding to an indivisual charachter. Ascii charachters 65 to 90 are Upper Case charachters. In your case you would want to split the string when the charachter does not fall within this range.

If you want to try this and post your example then I can give some more guidance but its hard to give more advide without first understanding if you are trying to achieve this with VBA or some other means.

answered Feb 09 '17 at 14:16

n4nite

459
4
19

well i am not that educated in visual basic (but i have some knowledge). I have tried using a vbscript.regexp code i found online, it almost works but the problem is that it would only separate letters not numbers – Feb 09 '17 at 14:24
I started trying to devise a non-VBA solution but realised it was starting to get too difficult when you encountered organic molecules like C6H10O5 with multi-digit numbers. – Tom Sharpe Feb 09 '17 at 15:13
Please do continue – Feb 09 '17 at 15:36
Well if it was OK to expand each letter or digit into a separate cell like C | l | 2 | H | O then reassemble them, it should certainly be practicable. Unfortunately I'm going to be out now for the rest of the day but could have a shot at it later. – Tom Sharpe Feb 09 '17 at 15:47
I'm not good enough to do this in vba, but what does spring to mind is having a list of the chemical symbols (Cl, He etc) and then matching through the list - next issue will be trapping the numbers. may give a better person an idea. – Solar Mike Feb 09 '17 at 18:36
Although annoying that some (H&He etc.) are substrings of others. – Tom Sharpe Feb 09 '17 at 19:09
@SolarMike that is what im trying to do yes – Feb 09 '17 at 22:55

Tom Sharpe · Accepted Answer · 2017-02-10T13:13:59.117

OK the algorithm in the end is very simple

If at any point in the formula you have a number, then look for the next capital letter and output all characters up to that point.

If at any point in the formula you have a letter, then look for the next capital letter *or number* and output all characters up to that point.

The formula is rather long

=IF(ISNUMBER(MID($A$1,SUM(LEN($B$1:B1))+1,1)+0),
MID(MID($A$1,SUM(LEN($B$1:B1))+1,9),1,MIN(FIND( MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROW($1:$26),1),MID($A$1,SUM(LEN($B$1:B1))+2,9)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"  ))),
MID(MID($A$1,SUM(LEN($B$1:B1))+1,9),1,MIN(FIND( MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",ROW($1:$36),1),MID($A$1,SUM(LEN($B$1:B1))+2,9)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"  ))))

must be entered as an array formula using CtrlShiftEnter and the 9 would need increasing ( or changing to len($a1) ) if the formula was longer than 9 characters.

Here's a shorter version that doesn't have to be entered as an array formula

=IF(ISNUMBER(MID($A1,SUMPRODUCT(LEN($B1:B1))+1,1)+0),
MID(MID($A1,SUMPRODUCT(LEN($B1:B1))+1,9),1,AGGREGATE(15,6,FIND( MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROW($1:$26),1),MID($A1,SUMPRODUCT(LEN($B1:B1))+2,9)&"A" ),1)),
MID(MID($A1,SUMPRODUCT(LEN($B1:B1))+1,9),1,AGGREGATE(15,6,FIND( MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789",ROW($1:$36),1),MID($A1,SUMPRODUCT(LEN($B1:B1))+2,9)&"A"),1)))

How do you separate text in excel vba by "CamelCase" and numbers

3 Answers3