Text to columns without delimiters

Mcstefan

New Member
Joined
May 17, 2014
Messages
42
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows
Not sure how this works with Chinese Characters. Use Power Query. Here is the Mcode to split the data

Power Query:
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,064
Office Version
  1. 2016
Platform
  1. Windows
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
ABCD
1GL#/GL Name/Amount - Header
210001收入0.589410001收入0.5894
3100011销售成本98.4578100011销售成本98.4578
Sheet927
Cell Formulas
RangeFormula
B2:B3B2=-LOOKUP(0,-LEFT(A2,ROW(A$1:A$15)))
C2:C3C2=MID(A2,LEN(B2)+1,MIN(FIND({0,1,2,3,4}+{0;5},A2&1/17,LEN(B2)+1))-LEN(B2)-1)
D2:D3D2=SUBSTITUTE(SUBSTITUTE(A2,B2,""),C2,"")+0


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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,633
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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})))
 
Last edited:

Mcstefan

New Member
Joined
May 17, 2014
Messages
42

ADVERTISEMENT

jtakw, that is genius !​

thank you very much.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,064
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You're welcome, thanks for the feedback.

Got shorter formulas here:

Book3.xlsx
ABCD
1GL#/GL Name/Amount - Header
210001收入0.589410001收入0.5894
3100011销售成本98.4578100011销售成本98.4578
Sheet927
Cell Formulas
RangeFormula
B2:B3B2=-LOOKUP(0,-LEFT(A2,ROW(A$1:A$15)))
C2:C3C2=SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,"")
D2:D3D2=-LOOKUP(0,-RIGHT(A2,ROW(A$1:A$15)))
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@jtakw, I believe you should be updated from "Well-known Member" to "Formula Genius". You are amazing, time and time again!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,064
Office Version
  1. 2016
Platform
  1. Windows
@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 :unsure:
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,633
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
You're welcome, thanks for the feedback.

Got shorter formulas here:
.....
C2:C3C2=SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,"")
I would suggest you use the formula I posted in Message #4 for C2 instead, namely, this one...
Excel Formula:
=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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,666
Members
425,367
Latest member
Boboka

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