Help with parsing string from right..

[lentilbake]

Hi there, I'm trying to pull out the last (right hand) part of some string data and I'm not having much luck.

I have a list of string data in column A like this:





COL A


3-nitrobenzenecarboxylic acid & its preparation 4A, 13A


3-nitrobenzoic acid and its preparation 4A, 13A




Acid chlorides and their preparation 41, 74, 96B


Acid rain, simulation 92





Partition coefficient / constant / function 36B, 100,


Patton and Reeder's indicator 32




1,2,3-indanetrione hydrate 66







Potassium manganate(VII) / propane-1,2,3-triol reaction 37






and I'd like to extract the last part of the strings to separate columns:


COL B
COL C
COL D
COL E
etc


4A
13A





4A
13A





41
74
96B




92






37
45B
46




36B
100






etc



There are sometimes just 1 number (or number letter combo like 96B) on the end and sometimes 4 5 or more.

This is a one off operation from a long list of some 2000 items so any quick and dirt method would do!

Thanks in advance for any help anyone can give me, I'm struggling because all the functions I commonly use work from the left!

(Apologies for the weird formatting above)

[p45cal]

Select the cells in column A then run this macro. See also the comments in the macro.

Sub blah()
For Each cll In Selection.Cells
xx = Replace(cll.Value, ",", "")
xx = Split(xx)
Start = 0
For i = 1 To UBound(xx)
If IsNumeric(Left(xx(i), 1)) Then
Cells(cll.Row, Columns.Count).End(xlToLeft).Offset(, 1).Value = xx(i)
If Start = 0 Then Start = WorksheetFunction.Find(" " & xx(i), cll.Value)
End If
Next i
'If Start > 0 Then cll.Value = Left(cll.Value, Start - 1) 'remove the apostrophe from the beginning of this line if you want to remove the numbers from the first column.
Next cll
End Sub
It helps if there are only blank cells to the right of the selected cells.
It detects what needs to be extracted by the first character being a number.

[p45cal]

Note a change made to the code:
For i = 0 To UBound(xx)
to:
For i = 1 To UBound(xx)

It might still get confused with the likes of propane-1,2,3-triol if there's a space before one of the numbers.

[mikerickson]

Try working right to left


Sub test()
Dim dataRange As Range, aCell As Range
Dim aEntry As String
Dim Words As Variant
Dim High As Long, i As Long
With Sheet1.Range("A:A")
Set dataRange = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each aCell In dataRange
With aCell
aEntry = WorksheetFunction.Trim(Replace(CStr(.Value), ",", " "))
Words = Split(aEntry, " ")
High = UBound(Words)

.Offset(0, 1).Resize(1, High).ClearContents

For i = High To 0 Step -1
If Words(i) Like "[!0-9]*" Then Exit For
.Offset(0, i).Value = Words(i)
Next i

On Error Resume Next
.Offset(0, 1).Resize(1, High).SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
On Error GoTo 0
End With
Next aCell
End Sub

[lentilbake]

Thanks for your very prompt reply, going to have a go with the above, will let you know how I got on.
Awkward data!

Thanks

[SamT]

Did that work for you? If yes, would you use the Thread tool at the top of this page to mark the thread "Solved?"

Thanks.

[lentilbake]

Many thanks to all who responded, both solutions seem to do the trick and will save me a lot of time.
Sorry for the delay in responding.
I've marked the thread as solved.