Formula to split text at each specific character, then return back to original format.

[brumby]

Hiya folks.

I have a template being output from our ERP system, a text ASCII file which can vary in number of fields depending on which template is used. I am using the ^ character to separate the fields. I know I can open the text file into excel and use the option to open into excel and look for ^ to split the info, however I am struggling to save back the file into a format which will allow upload back to the system. Hopefully below will explain.

The is output from system into a text doc, which I can copy paste into Sheet1 A1

I^0-02-0100100^AUK01^12^3^52^3^2^BNT^3^30^0^2

So 13 fields separated by ^

So in sheet 2, I would like to have a formula which output as per below

A1 = I
B1 = 0-02-0100100
C1 = AUK01
D1= 12
E1 = 3 - I would like to amend this value to 9
F1 = 52
G1 = 3
H1 = 2
I1 = BNT - I would like to amend this value to MRP
J1 = 3
K1 = 30
L1 = 0 - I would like to amend this value to 4
N1 = 2

then on sheet 3, would like to put it all back together again so I can copy paste back into text doc and upload the changes. so in CELL A1 it would return

I^0-02-0100100^AUK01^12^9^52^3^2^MRP^3^30^4^2

is this at all possible either with formula or maybe VBA?

hopefully this is possible, however i think this is very difficult.

Many Thanks guys!!!!!

 

[BQardi]

No very easy...
With formula:

='Sheet2'!A1&"^"&'Sheet2'!B1&"^"&'Sheet2'!C1&"^"&'Sheet2'!D1&"^"&'Sheet2'!E1 ... etc.

With macro (a UDF to concatenate with a delimiter):

Function DConcat(ConcatRng As Range, Delimiter As String) As String
Dim ConcatArr As Variant, Arr As Variant, TmpStr As String
Set ConcatArr = ConcatRng
For Each Arr In ConcatArr
    If Not Arr = vbNullString Then TmpStr = TmpStr & Arr & Delimiter
Next Arr
DConcat = Left(TmpStr, Len(TmpStr) - Len(Delimiter))
End Function

Usage:

=DConcat('Sheet2'!A1:N1,"^")

 

[BQardi]

Woops, a little mistake there:

Set ConcatArr = ConcatRng

should be:

ConcatArr = ConcatRng

Without Set, as ConcatArr should be an array and not a range

 

[Fennek]

Hi,

for a test all in one sheet

Sub Main
Ar = split(cells(1,1), "^")
cells(2,1).resize(ubound(Ar)) = application.transpose(Ar)
End Sub

sub iJoin
Tx = join(application.transpose(Range("A2:A13")), "^")
cells(15,1) = Tx
end sub

regards

 

[BQardi]

Hi,

for a test all in one sheet

Sub Main
Ar = split(cells(1,1), "^")
cells(2,1).resize(ubound(Ar)) = application.transpose(Ar)
End Sub

sub iJoin
Tx = join(application.transpose(Range("A2:A13")), "^")
cells(15,1) = Tx
end sub

regards

I really like this solution. Very simple, yet effective.
But the last value is missing, so you would have to add +1 to the resize function:

Sub Main()
ar = Split(Cells(1, 1), "^")
Cells(2, 1).Resize(UBound(ar)[COLOR=#ff0000] + 1[/COLOR]) = Application.Transpose(ar)
End Sub


Sub iJoin()
Tx = Join(Application.Transpose(Range("A2:[COLOR=#ff0000]A14[/COLOR]")), "^")
Cells(15, 1) = Tx
End Sub

 

[brumby]

i must be doing something wrong, cant get it to work, can someone step through the process i should follow?

many thanks for your help

 

[BQardi]

You need to specify the sheets.
What I understand is that something like this is what you want:

Sub Main()
ar = Split([B][COLOR=#ff0000]Sheet1[/COLOR][/B].Cells(1, 1), "^")
[B][COLOR=#0000ff]Sheet2[/COLOR][/B].Cells(1, 1).Resize(UBound(ar) + 1) = Application.Transpose(ar)
End Sub

Sub iJoin()
Tx = Join(Application.Transpose([B][COLOR=#0000ff]Sheet2[/COLOR][/B].Range("A1:A13")), "^")
[COLOR=#008000][B]Sheet3[/B][/COLOR].Cells(1, 1) = Tx
End Sub

Note this is restricted to 13 fields as per your example. This can easily be changed to suit a dynamic number of fields if you need it.

Assign macros Main and iJoin to buttons or run with Alt+F8

First, run Main
Then do your corrections
Then run iJoin