Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating data in the same cell
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating data in the same cell
"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 --------------- Jacob Skaria "Jor" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating data in the same cell | Excel Worksheet Functions | |||
Separating data in the same cell | Excel Programming | |||
Separating Data in one cell to multiple cells | Excel Discussion (Misc queries) | |||
Moving cell data and separating or deleting | Excel Worksheet Functions | |||
Separating data in a cell | Excel Discussion (Misc queries) |