Horizontal String Extract

[knobi]

Hello !!

Is it possible to this on Excel ?

cells contents:

Cell A has:  ab[COLOR="Red"]2[/COLOR]cd.e[COLOR="Red"]4[/COLOR]fg[COLOR="Red"]5[/COLOR]hij

Result for cell B would be:   abcd.efghij    
and for cell C:  245

Note1: numbers may change its position on cells contents
Note2: amount of characters on a cell also may change...

 

[daverunt]

Hi,

I found this link
http://www.ozgrid.com/forum/showthread.php?t=45743
and modified the same function to extract the number.
Maybe you can tidy it up to incorporate both in one.

Sub Split()
 
Range("B1").Value = StripNumber(Range("A1").Value)
Range("C1").Value = StripText(Range("A1").Value)
End Sub
 
 
Function StripNumber(stdText As String)
    Dim str As String, i As Integer
'strips the number from a longer text string
    stdText = Trim(stdText)
    For i = 1 To Len(stdText)
        If Not IsNumeric(Mid(stdText, i, 1)) Then
        str = str & Mid(stdText, i, 1)
        End If
    Next i
 
    StripNumber = str ' * 1
 
End Function
 
Function StripText(stdText As String)
    Dim str As String, i As Integer
 'strips the text from a text string leaving any numbers
    stdText = Trim(stdText)
 
    For i = 1 To Len(stdText)
        If IsNumeric(Mid(stdText, i, 1)) Then
        num = num & Mid(stdText, i, 1)
 
        End If
    Next i
 
    StripText = num
 
End Function

 

[Firefly2012]

What version of Excel are you using? You can use a wholly formula-based approach if using Excel2007+

 

[daverunt]

Firefly can you post that anyway as I use 2007 and would be interested.
Cheers.

 

[Firefly2012]

Hi Dave

Here's one possibility:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>ab2cd.e4fg5hij</TD><TD>abcd.efghij</TD><TD style="TEXT-ALIGN: right">245</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C1</TH><TD style="TEXT-ALIGN: left">=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
2007+ is required for the level of nesting in the B1 formula (ie exceeds the 7 nested levels limit in 2003)

 

[daverunt]

Brilliant. Thanks for the information.

The first one easy enough to work out what's going on.
The second one might take me a few days....

 

[knobi]

Folks sorry about the delay, pretty busy week...anyway, the first forumula removing the numbers works perfectly. But for the second one, i think there is bug.....

When typing :

dij023r8y8h082hihj0d3u8308

the following output on C1 is:

238808203830

There is a missing 0 at the beginning and a missing 8 at the end. (after formatting the cell as numeric with no decimal places.

I'll continue monitoring this thread and i'm still working on this formula. I'll let you guys know once i find a solution.
If anyone wants to step up and suggest a resolution, be welcome.

 

[Firefly2012]

Sorry, I took a shortcut and made an assumption that you would have cell contents of 25 characters or fewer. The following will handle cell contents of any length. Note, however, that if you have more than 15 digits in any single cell, it will only return the first 15. This is a limitation brought about by Excel's limited precision, not an actual issue with the formula itself. If this is a problem, you would need a UDF coded in VBA to overcome.

 

[Firefly2012]

Ok try this:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">dij023r8y8h082hihj0d3u8308</td><td style=";">02388082038308</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">SUMPRODUCT(<font color="Red">MID(<font color="Green">"01"&A1,LARGE(<font color="Purple">INDEX(<font color="Teal">ISNUMBER(<font color="#FF00FF">--MID(<font color="Navy">1&A1,ROW(<font color="Blue">INDIRECT(<font color="Red">"1:"&LEN(<font color="Green">A1</font>)+1</font>)</font>),1</font>)</font>)*
ROW(<font color="#FF00FF">INDIRECT(<font color="Navy">"1:" & LEN(<font color="Blue">A1</font>)+1</font>)</font>),0</font>),ROW(<font color="Teal">INDIRECT(<font color="#FF00FF">"1:" & LEN(<font color="Navy">A1</font>)+1</font>)</font>)</font>)+1,1</font>)*10^ROW(<font color="Green">INDIRECT(<font color="Purple">"1:" & LEN(<font color="Teal">A1</font>)+1</font>)</font>)/10</font>),2,255</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Michael M]

You could also use a UDF
This one from Jindon

UDF
1) Hit Alt + F11 to open VBE
2) go to [Insert] - [Module] then paste the code onto the right pane
3) hit Alt + F11 again to get back to Excel
Use in cell like
=AlphaNum(A1,True) '<- True for Alphabets, False for Numbers

Function AlphaNum(txt As String, Optional Alpha As Boolean = True) String
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(Alpha, "\d+", "\D+")
    .Global = True
    AlphaNum = .replace(txt,"")
End With
End Function