Cut delimited characters and apply numerical formats

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
The below code is working for me but I need to make a few adjustments which I can't figure out. I have one column where a cell contains 4 delimited values and a dollar value. eg 22-4-3-7 $217560.00

My aim is to cut the delimited values into each of 4 different columns I have inserted leaving the original cell with only the dollar value.

Essentially the code works but has two minor issues.
1. The delimited values are copied to the columns correctly but aren't delete from the originating cell. Also the last value takes over itself plus the dollar value. So in the above example this would copy 7 $217560.00 to column 4 instead of the desired 7 being copied to column 4.
2. The numbers when copied to the new columns are in text format and dollar vlaue
the formats I am trying for are
Code:
Selection.NumberFormat = "$#,##0.00"
Selection.NumberFormat = "0"
Code:
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Table1[[#Headers],[career]]").Select
ActiveCell.FormulaR1C1 = "car_earn"
Range("Table1[[#Headers],[Column4]]").Select
ActiveCell.FormulaR1C1 = "car_start"
Range("Table1[[#Headers],[Column3]]").Select
ActiveCell.FormulaR1C1 = "car_first"
Range("Table1[[#Headers],[Column2]]").Select
ActiveCell.FormulaR1C1 = "car_second"
Range("Table1[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "car_third"
With Sheets("Sheet1")
Dim arr As Variant, x As Long
arr = .Range("AH:AH" & .UsedRange.Rows.Count)
For x = 1 To UBound(arr)
.Range("AI" & x & ":AL" & x).Value = Split(arr(x, 1), "-")
Next x
End With
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Is there anything i can explain or clarify that would help develop a good answer?
 

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Is there a way to put a stop in this code
Code:
.Range("AI" & x & ":AL" & x).Value = Split(arr(x, 1), "-")
So that when it gets to the end it has an out.
Code:
.Range("AI" & x & ":AL" & x).Value = Split(arr(x, 1), "-").Until("$") Then Next X
or something similar.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,781
Hi

Assuming there's a space separing the 4th number and the dollar value, like in your example, try:

Code:
    .Range("AI" & x & ":AL" & x).Value = Split(Left(arr(x, 1), InStr(arr(x, 1), " ")), "-")
 

Sayth

Board Regular
Joined
Jun 4, 2010
Messages
212
Awesome that works but is it possible to remove the delimited values from the originating cell to leave only the dollar value?
Current output

Code:
<table style="border-collapse: collapse; width: 510pt;" border="0" cellpadding="0" cellspacing="0" width="680"><col style="width: 102pt;" span="5" width="136"><tr style="height: 15pt;" height="20">   <td class="xl63" style="height: 15pt; width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid white; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20" width="136">22-4-3-7 $217560.00</td>   <td class="xl63" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">22</td>   <td class="xl63" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">4</td>   <td class="xl63" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">3</td>   <td class="xl63" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">7 </td> </tr></table>
Aimed output
Code:
<table style="border-collapse: collapse; width: 510pt;" border="0" cellpadding="0" cellspacing="0" width="680"><col style="width: 102pt;" span="5" width="136"><tr style="height: 15pt;" height="20">   <td class="xl65" style="height: 15pt; width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border: 0.5pt solid white; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="20" width="136">$217560.00</td>   <td class="xl65" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">22</td>   <td class="xl65" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">4</td>   <td class="xl65" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">3</td>   <td class="xl65" style="width: 102pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; border-width: 0.5pt 0.5pt 0.5pt medium; border-style: solid solid solid none; border-color: white white white -moz-use-text-color; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="136">7 </td> </tr></table>
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,781
To get the dollar amount try:

Code:
    .Range("AI" & x & ":AL" & x).Value = Split(Left(arr(x, 1), InStr(arr(x, 1), " ")), "-")
    .Range("AH" & x).Value = Mid(arr(x, 1), InStr(arr(x, 1), "$") + 1)
Notice that the result of the split is text. You can convert it to numbers (assuming those 4 columns have the numberfomat "0", as you posted) with:

Code:
    .Range("AI" & x & ":AL" & x).Value = .Range("AI" & x & ":AL" & x).Value
Also don't forget to have column H formatted as dollar amount.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,934
Messages
5,508,220
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top