Splitting cell values based on 12th space from the right

[rafmirza]

Hi All,

I currently have data sitting in the first column A in the following format representing line items from Period 1 to 12

4-4030 Fees and Charges-Unrestricted 5,623 5,811 5,610 5,733 7,563 5,417 5,484 6,152 10,962 4,487 4,835 4,412 72,091 73,660 (1,570)
4-5035 Recoupments 0 0 0 0 0 530 0 183 0 0 0 0 713 0 713
6-0040 Assets Purchased<$5000 0 0 0 0 0 0 0 0 0 0 0 0 0 1,850 1,850

I'm looking to split A1 to A by splitting off the cell using the 12th space from the right, which will give me the numbers in cell to further delimit based on space only. The line item description and code at the start are no problems, can use them as they are (but if there's a way to cut off the "4-4030" part as well that would be cool!)

Any ideas on how to do this whilst preserving the brackets indicating negative values and commas?

 

[kweaver]

In B1 (fill down) I did a call to a UDF:

Function Reversestr(str As String) As String
Reversestr = StrReverse(Trim(str))
End Function

Then, in C1 (fill down) I found the 12th space:

=SEARCH(CHAR(127),SUBSTITUTE(B1," ",CHAR(127),12))

Then, in D1 (fill down)

I used the UDF again on the reversed text:

=Reversestr(LEFT(B1,C1))

I think this does it, yes?

You could change the 12 in the C column if you want more data from the right.

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub SplitAt12thSpaceFromRightRemove1stPart()
  Application.ScreenUpdating = False
  On Error GoTo SomethingWentWrong
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",SUBSTITUTE(SUBSTITUTE(@,"" "",""|"",LEN(@)-LEN(SUBSTITUTE(@,"" "",""""))-11),"" "",""|"",1))", "@", .Address))
    .TextToColumns , xlDelimited, , , False, False, False, False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3, 1))
  End With
SomethingWentWrong:
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[rafmirza]

Rick, thanks for your solution. Somehow, the formula below worked and I was able to pull out each period into a separate column by changing the "12" into different numbers to pull each out one by one, saving me the hassle to run the delimiter process.

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255*(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-12),255))