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.
 

jtakw

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

That's where I have the advantage, I read, write, and speak Chinese, see my comments at the bottom of Post #3, the figures on the right of the text string is a monetary value, therefore, trailing 0 s mean nothing. ;)
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're right @Rick Rothstein , you were saying C2, and I was thinking D2 :oops: , so I don't know if the text strings may have trailing zeros, but whether it would or not, your formula for C2 would be more Robust and reliable, it's supposed to be dinner time for me 2 hours ago, and I'm running back and forth between the kitchen and my computer, I'm still trying to cook :)
 

Mcstefan

New Member
Joined
May 17, 2014
Messages
42
@jtakw, I have tested all the formulas and I have retained your first suggestion. Your second suggestion (shorter formulas) did not produce the expected result on this line
140505 在产品30000
140505​
在产品3
0​

May I get your help on refining the formulas? Here is an example where none of the formulas worked. As you see, the negative sign remained in the second column instead of the third one.
2705 税收应付-235000
2705​
税收应付-235,000
2711 版权应付-165423.74
2711​
版权应付-165,424
 

Mcstefan

New Member
Joined
May 17, 2014
Messages
42

ADVERTISEMENT

@jtakw, sorry, your first suggestion did not work on this line

410418 未分配利润-2020-1190557.91631337
410418​
未分配利润-
#VALUE!​

However, the sorter formulas produced a better (but not perfect) result. In this case I would retain the shorter formulas, but I would need a fix on the negative sign. This one is tricky, the first "-" symbol is the hyphen (and should be part of the second column) and the second "-" symbol is the negative sign.
410418 未分配利润-2020-1190557.91631337
410418​
未分配利润-2020-1,190,558
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,343
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
What would the text have looked like if that last number was not negative... would there have been a space or a plus sign there?

Also, that last number contains 15 digits along with the decimal point so it has more digits than Excel can display. Should the number be retained as a number with digits truncated or is the number presented as Text acceptable in order to retain all of the digits? By the way, none of the formulas work for it because none of your examples showed text with a number containing so many digits.
 

Mcstefan

New Member
Joined
May 17, 2014
Messages
42

ADVERTISEMENT

What would the text have looked like if that last number was not negative... would there have been a space or a plus sign there?
It would have never had a "+" plus sign for positive numbers, but could have or have not a space " " in front of it. The space " " would have been part of the second column in that case, along with the Chinese name.
Also note that some numbers do not have the "." for the decimals - see my example "140505 在产品30000"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,343
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I could not come up with an Excel formula that could handle 15-digit floating point numbers, so I made a UDF (user defined function) that you can use instead...
VBA Code:
Function Fields(Txt As String, FldNum As Long) As String
  Dim N As Long, LeadNum As String, EndNum As String
  LeadNum = Val(Txt)
  For N = Len(Txt) To 1 Step -1
    If Not IsNumeric(Mid(Txt, N)) Then
      EndNum = Mid(Txt, N + 1)
      Exit For
    End If
  Next
  Select Case FldNum
    Case 1
      Fields = Trim(LeadNum)
    Case 2
      Fields = Trim(Replace(Replace(Txt, EndNum, ""), LeadNum, ""))
    Case 3
      Fields = Trim(EndNum)
  End Select
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example, assuming your data is located in Column A starting on Row 2, put this formula in cell B2 and copy it across to cell D2, then copy cells B2:D2 down to the end of your data...

=Fields($A2,COLUMN(A2))

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

Forum statistics

Threads
1,141,062
Messages
5,704,062
Members
421,326
Latest member
pfaustino

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