On Mon, 25 May 2009 04:11:01 -0700, Elton Law
wrote:

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

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

Befo
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