Copy text and numbers from one cell and paste them into multiple cells

[salman350z]

Hi,

I have one column with 938 rows in Excel 2010. The vast majority(90%) of cells look like: " 2 B/W GGG". I want to use VBA to separate the "2", which is a quantity, "B/W", which is a type of cut, and "GGG", which is detail, and place these values in separate columns(2-4) as needed.

The problem is, not all of the data is consistent. For example, 90% of the cells looks like the previous example, while others look like: "Solid", or "Solid BBB B/W".

For the cells containing "solid", I would leave it under type of cut, and for the cells containing " Solid BBB B/W", I would split them into type of cut and details.

Any help would be greatly appreciated, I am new to VBA.

 

[hiker95]

salman350z,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


The following macro is based on your posted text display.

Sample raw data:

Excel 2007
	A	B	C	D
1		quantity	type of cut	detail
2	2 B/W GGG
3	Solid
4	Solid BBB B/W
5
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1		quantity	type of cut	detail
2	2 B/W GGG	2	B/W	GGG
3	Solid		Solid
4	Solid BBB B/W		Solid	BBB B/W
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ExtractData()
' hiker95, 11/14/2014, ME818380
Dim c As Range, s
Application.ScreenUpdating = False
For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
  If InStr(Trim(c), " ") Then
    s = Split(Trim(c), " ")
    If IsNumeric(s(0)) Then
      c.Offset(, 1) = s(0)
      c.Offset(, 2) = s(1)
      c.Offset(, 3) = s(2)
    ElseIf s(0) = "Solid" Then
      c.Offset(, 2) = "Solid"
      c.Offset(, 3) = s(1) & " " & s(2)
    End If
  ElseIf c = "Solid" Then
    c.Offset(, 2) = "Solid"
  End If
Next c
Columns(2).Resize(, 3).AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ExtractData macro.

 

[salman350z]

hiker 95,

I am using a PC with Windows 7 and excel 2010. I really appreciate your fast response, I will give this a go first thing tomorrow and let you know of the results.

 

[hiker95]

salman350z,

Thanks for the feedback.

See you tomorrow.