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

brumby

Active Member
Joined
Apr 1, 2003
Messages
398
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!!!!!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
No very easy...
With formula:
Code:
='Sheet2'!A1&"^"&'Sheet2'!B1&"^"&'Sheet2'!C1&"^"&'Sheet2'!D1&"^"&'Sheet2'!E1 ... etc.

With macro (a UDF to concatenate with a delimiter):
Code:
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:
Code:
=DConcat('Sheet2'!A1:N1,"^")
 
Last edited:

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
Woops, a little mistake there:
Code:
Set ConcatArr = ConcatRng
should be:
Code:
ConcatArr = ConcatRng
Without Set, as ConcatArr should be an array and not a range
 

Fennek

Active Member
Joined
Nov 21, 2016
Messages
279
Hi,

for a test all in one sheet
Code:
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
 
Last edited:

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483

ADVERTISEMENT

Hi,

for a test all in one sheet
Rich (BB code):
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:
Code:
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

Active Member
Joined
Apr 1, 2003
Messages
398
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

Active Member
Joined
Aug 30, 2016
Messages
483
You need to specify the sheets.
What I understand is that something like this is what you want:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,536
Messages
5,659,376
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top