Text to columns without delimiters

[Mcstefan]

Hi,

I would like to separate the following list from one column into three columns.

GL#/GL Name/Amount - Header

10001收入0.5894

100011销售成本98.4578

here is the structure of the given list:
- first part of characters are always numeric, but they could have a different length
- second part is always Chinese characters and they also have a different length
- third part is always a number with multiple decimals

I think that one idea would be to insert a blank space between the three parts and then do Text to Columns Delimited by " ".
is it possible to achieve this just using Excel formulas?

Thanks,
Cristian.

 

[alansidman]

Not sure how this works with Chinese Characters. Use Power Query. Here is the Mcode to split the data

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GL#/GL Name/Amount - Header", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "GL#/GL Name/Amount - Header", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"GL#/GL Name/Amount - Header.1", "GL#/GL Name/Amount - Header.2", "GL#/GL Name/Amount - Header.3"})
in
    #"Split Column by Character Transition"

 

[jtakw]

Hi,

Here are formula options.
Can't think of shorter ways to parse the Chinese characters yet, will post again if I do ( I can read them thou )

Book3.xlsx
	A	B	C	D
1	GL#/GL Name/Amount - Header
2	10001收入0.5894	10001	收入	0.5894
3	100011销售成本98.4578	100011	销售成本	98.4578
Sheet927

Cell Formulas
Range	Formula
B2:B3	B2	=-LOOKUP(0,-LEFT(A2,ROW(A$1:A$15)))
C2:C3	C2	=MID(A2,LEN(B2)+1,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17,LEN(B2)+1))-LEN(B2)-1)
D2:D3	D2	=SUBSTITUTE(SUBSTITUTE(A2,B2,""),C2,"")+0

First one is "Income"
Second is "Cost of Goods Sold"
Am I right

 

[Rick Rothstein]

Assuming your data is in Column A starting on Row 2, put these formulas in the indicated cells and copy down...

B2: =LOOKUP(9.9E+307,--LEFT(A2,ROW($1:$99)))

C2: =MID(LEFT(A2,FIND(D2,A2)-1),LEN(B2)+1,99)

D2: =-LOOKUP(1,-(" "&RIGHT(SUBSTITUTE(A2,"E","X"),{1,2,3,4,5,6,7,8,9,10,11,12})))

 

[Mcstefan]

jtakw, that is genius !​

thank you very much.

 

[Mcstefan]

Rick Rothstein, that is amazing too!​

 

[jtakw]

You're welcome, thanks for the feedback.

Got shorter formulas here:

Book3.xlsx
	A	B	C	D
1	GL#/GL Name/Amount - Header
2	10001收入0.5894	10001	收入	0.5894
3	100011销售成本98.4578	100011	销售成本	98.4578
Sheet927

Cell Formulas
Range	Formula
B2:B3	B2	=-LOOKUP(0,-LEFT(A2,ROW(A$1:A$15)))
C2:C3	C2	=SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,"")
D2:D3	D2	=-LOOKUP(0,-RIGHT(A2,ROW(A$1:A$15)))

 

[johnnyL]

@jtakw, I believe you should be updated from "Well-known Member" to "Formula Genius". You are amazing, time and time again!

 

[jtakw]

@johnnyL , Thank you very much for your kind words, there are many members on this board that are Amazing at one or more aspects of Excel, I'm often humbled and learn also from their responses to Poster's questions. I'm very happy to see that you find my postings helpful, and I hope I can continue to do so and try to provide exceptional help to those who seek it, where I can be of assistance.

As for an upgrade in title, the MODs and ADMINs would have to agree

 

[Rick Rothstein]

You're welcome, thanks for the feedback.

Got shorter formulas here:
.....

C2:C3

C2

=SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,"")

I would suggest you use the formula I posted in Message #4 for C2 instead, namely, this one...

=MID(LEFT(A2,FIND(D2,A2)-1),LEN(B2)+1,99)

Your formula could produce incorrect values if the trailing number contained trailing zeros. For example...

100011销售成本98.4000