Cut delimited characters and apply numerical formats

[Sayth]

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

Selection.NumberFormat = "$#,##0.00"
Selection.NumberFormat = "0"

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

 

[Sayth]

Is there anything i can explain or clarify that would help develop a good answer?

 

[Sayth]

Is there a way to put a stop in this code

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

So that when it gets to the end it has an out.

.Range("AI" & x & ":AL" & x).Value = Split(arr(x, 1), "-").Until("$") Then Next X

or something similar.

 

[pgc01]

Hi

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

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

 

[Sayth]

Awesome that works but is it possible to remove the delimited values from the originating cell to leave only the dollar value?
Current output

<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

<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]

To get the dollar amount try:

    .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:

    .Range("AI" & x & ":AL" & x).Value = .Range("AI" & x & ":AL" & x).Value

Also don't forget to have column H formatted as dollar amount.