Split by characters

[Elton Law]

Dear Expert,
How to split a cell into 3 parts if they are like this please ?

Before:
9j36 in one cell
After:
9 j 36 in 3 different cells

Before:
10°32 in one cell
10 ° 32 in 3 different cells

Thanks

 

[OssieMac]

Hi Elton,

Try Data -> Text to columns.

 

[Elton Law]

Hi OssieMac,
Sometimes, the text in a cell is ...
9j36 in one cell
12k27 in one cell
23m13 in one cell
1°26
Honestly, I want to split numerical figure in front of the Alphabet or ° in
one column.
Put alphabet or ° in one column
Numerical figure after the Alphabet or ° in one column
Then make it from 1 cell to 3 columns.

Is that feasible ?

 

[Jacob Skaria]

Below is a UDF to split....Try and feedback

A1 = 23m13
B1 = Splitbynumbers($A1,1)
C1 = Splitbynumbers($A1,2)
D1 = Splitbynumbers($A1,3)


Function SplitbyNumbers(varRange, intPos As Integer) As String
Dim intTemp As Integer
Dim intStep As Integer
Dim arrTemp(3) As Variant

For intTemp = 1 To Len(varRange)
If IsNumeric(Mid(varRange, intTemp, 1)) Then
intStep = IIf(arrTemp(2) = Empty, 1, 3)
arrTemp(intStep) = arrTemp(intStep) & Mid(varRange, intTemp, 1)
Else
arrTemp(2) = arrTemp(2) & Mid(varRange, intTemp, 1)
End If
Next
SplitbyNumbers = arrTemp(intPos)
End Function

 

[Ron Rosenfeld]

Dear Expert,
How to split a cell into 3 parts if they are like this please ?

Before:
9j36 in one cell
After:
9 j 36 in 3 different cells

Before:
10°32 in one cell
10 ° 32 in 3 different cells

Thanks

You can use a Macro.


To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range to process. Then <alt-F8>
opens the macro dialog box. Select the macro by name, and <RUN>.

========================================
Option Explicit

Sub SplitByNumbers()
Dim c As Range, rg As Range
Dim re As Object, mc As Object, m As Object
Dim i As Long
Set rg = Selection 'or however you want to
'select the range to process


Set re = CreateObject("vbscript.regexp")
re.Pattern = "(\d+)(\D+)(\d+)"

For Each c In rg

If re.test(c.Value) Then
Set mc = re.Execute(c.Value)
For i = 1 To mc(0).submatches.Count

'The line below puts the split values next to the original
'To replace the original, use the commented out line instead:
' c.Offset(0, i-1).Value = mc(0).submatches(i - 1)

c.Offset(0, i).Value = mc(0).submatches(i - 1)
Next i
End If

Next c

End Sub
==========================================
--ron

 

[Rick Rothstein]

If you are willing to use a macro as others have suggested, then here is my
macro offering for you to consider...

Sub SplitIt()
Dim R As Range
Dim S As String
For Each R In Selection
S = R.Value
If Len(S) Then
R.Offset(, 1).Value = Val(S)
R.Offset(, 3).Value = StrReverse(Val(StrReverse(S)))
R.Offset(, 2).Value = Replace(Split(Split(S, R.Offset(, 1).Value) _
(1), R.Offset(, 3).Value)(0), "0", "")
End If
Next
End Sub

The only thing this macro won't handle (as presently written) is numbers
with decimal points in them.

 

[Elton Law]

Hi all,
I have tested (functions + marcos).
That's really amazing. Before that, I don't believe that can be done ....
Anyway, made it !
Thanks indeed ....... Thanks

 

[Greg Lovern]

Hi Elton,

To do it in a formula without having to write a custom function in
VBA:

With your text in column A:

9j36
12k27
23m13
1°26
1234a1
1a1234
1abc23
12°a°b°c°3

In column B:
=itSEARCH(A1,"[0-9]{1,}",,3)
Results:
9
12
23
1
1234
1
1
12

In column C:
=itSEARCH(A1,"[^0-9]{1,}",,3)
Results:
j
k
m
°
a
a
abc
°a°b°c°

In column D:
=itSEARCH(A1,"[0-9]{1,}",,3,,,,,TRUE)
Results:
36
27
13
26
1
1234
23
3



To get the itSEARCH() function, you'll need to download and install
the Free Edition of inspector text:
(it never expires)
http://precisioncalc.com/it


For more information on the itSEARCH function:
http://precisioncalc.com/it/itSEARCH.html


Good luck with your project!


Greg Lovern
http://PrecisionCalc.com
More Power In Excel