# Text to columns without delimiters

#### Mcstefan

##### New Member
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
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"

#### Rick Rothstein

##### MrExcel MVP
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

##### Well-known Member
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

##### New Member
@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

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

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

Many thanks!

Replies
1
Views
98
Replies
0
Views
213
Replies
12
Views
423
Replies
1
Views
857
Replies
5
Views
393

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.

### Which adblocker are you using?

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

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