Separating data in the same cell

[Jor]

I'm needing to separate some data in columns. Here is a sample:
Thomas, Nehemiah JR 1,837.05 ; Tiller, Tanika 2,163.72 ; Tobin, Terri
1,182.66 ;

That is all in one cell. The individual entries are separated by semicolons.
I need to get the names in separate cells below each other in the same
column, and the numbers next to them in the same row and not in the same cell
with the names. Like this:


Column A - Thomas, Nehemiah JR Column B - 1,837.05
Column A -Tiller, Tanika Column B - 2,163.72
Column A -Tobin, Terri Column B - 1,182.66

Is there a formula I could use, or a way to do that automatically? Any way
at all to at least avoid separating the names manually and then separating
the numbers manually and deleting them in the other cell?

That might be incredibly difficult, so here is a hopefully easier problem
related to my first. This is an example of another cell which I need to
separate data:
TRIUMPH LEARNING 2539

This data is in the same cell, I need to get it in two separate cells, and
the number deleted from the first cell, like above. I know that I can use the
RIGHT function to get the number in the cell next to it, but is there a way
to then make it so that the number is deleted from the cell with the words?

 

[Jacob Skaria]

"Jor"

Try the below macro. Try with both combinations placing your data in ColA
from Row1. The extracted information will be available in ColB and ColC.. Try
and feedback..

A1 = Thomas, Nehemiah JR 1,837.05 ; Tiller, Tanika 2,163.72 ; Tobin, Terri
1,182.66 ;

A2 = TRIUMPH LEARNING 2539

If you are new to macros; set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the below code. Save. Get back to
Workbook. Run macro from Tools|Macro|Run <selected macro()>


Sub SeparateData()

Dim lngRow As Long, lngLastRow As Long, lngTemp As Long
Dim arrData As Variant
Dim strTemp As String
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
If Range("A" & lngRow) <> "" Then
arrData = Split(WorksheetFunction.Trim(Range("A" & lngRow)))
For intTemp = 0 To UBound(arrData)
If IsNumeric(arrData(intTemp)) Then
lngTemp = lngTemp + 1
Range("b" & lngTemp) = Trim(Replace(strTemp, ";", ""))
strTemp = ""
Range("c" & lngTemp) = Trim(arrData(intTemp)) + 0
Else
strTemp = strTemp & " " & arrData(intTemp)
End If
Next
End If
Next

End Sub

If this post helps click Yes