Separate elements from numbers in chemical formulas

[guilhermecruz]

Hello,

I have looked at many topics on how to separate numbers from letters in different columns, but I couldn´t find a solution for my problem. I need to take a chemical formula in a cell, for example Fe2O3, and separate each element and number into different cells, like: Fe 2 O 3

Another example: NaCl to Na 1 Cl 1


Thank you!

 

[diddi]

excellent suggestion. mark this thread "Solved"

 

[Michael M]

Too late now !!....on the 3rd bottle of Yellowglen....

 

[pgc01]

Notice that the effects of too much wine or regex are similar: first dizziness and then headache!

 

[guilhermecruz]

Thank you all for the help pgc01,

an alternative way (given to me by a friend), this time separating into different cells would be:



Option Explicit

Sub test()
Dim r As Range, m As Object, t As Long
Const myPtn As String = "([A-Z][a-z]?)(\d+)?"
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = myPtn
For Each r In Selection
t = 0
For Each m In .Execute(r.Value)
t = t + 2
r(, t).Value = m.SubMatches(0)
r(, t + 1).Value = _
IIf(m.SubMatches(1) = "", 1, m.SubMatches(1))
Next
Next
End With
End Sub

 

[pgc01]

I'm glad your problem is solved. Thanks for the feedback.

 

[pgc01]

P. S.

If I'm not mistaken you can do a small simplification in your pattern:

"([A-Z][a-z]?)(\d+)?"

to

"([A-Z][a-z]?)(\d*)"

 

[Rick Rothstein]

If anyone is interested, here is a non-RegExp function that will produce the same space delimited string that PGC's code produces.

Function ChemParts(ByVal S As String) As String
  Dim X As Long
  ChemParts = Left(S, 1)
  X = 2
  Do While X <= Len(S)
    If Mid(S, X - 1, 2) Like "[A-Za-z][A-Z]" Then S = WorksheetFunction.Replace(S, X, 0, 1)
    ChemParts = ChemParts & IIf(Mid(S, X - 1, 2) Like "[0-9a-z][A-Z]" Or _
                Mid(S, X - 1, 2) Like "[!0-9][0-9]", " ", "") & Mid(S, X, 1)
    X = X + 1
  Loop
  If Right(ChemParts, 1) Like "[!0-9]" Then ChemParts = ChemParts & " 1"
End Function