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.

 

[jtakw]

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...

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.

 

[Rick Rothstein]

the figures on the right of the text string is a monetary value, therefore, trailing 0 s mean nothing.

Does that mean there cannot be trailing zeros in the text?

 

[jtakw]

You're right @Rick Rothstein , you were saying C2, and I was thinking D2 , 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]

@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]

@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]

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]

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]

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...

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.

 

[Mcstefan]

Rick Rothstein, the VBA Code works like a charm!​

Many thanks!