Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
Hi Elton,
Try Data - Text to columns. -- Regards, OssieMac "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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
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 ? "OssieMac" wrote: Hi Elton, Try Data - Text to columns. -- Regards, OssieMac "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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
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 -- If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: 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 ? "OssieMac" wrote: Hi Elton, Try Data - Text to columns. -- Regards, OssieMac "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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
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. -- Rick (MVP - Excel) "Elton Law" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Elton Law" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Split by characters
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 On May 25, 4:11*am, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert 5 characters in a cell to 6 characters by adding a zero | Excel Discussion (Misc queries) | |||
Insert Leading Characters If String Is Only 7 Characters | Excel Discussion (Misc queries) | |||
How do I remove split a split window? | New Users to Excel | |||
Split field based on number of characters and space | Excel Worksheet Functions | |||
In Excel find characters when multiple characters exist w/i a cel | Excel Worksheet Functions |