Hi,
With the full string in Column A, and headers 'Full String' in A1,
'Text' in B1 and 'Numbers' in C1, the following code will do the split
You seem to have leading and trailing spaces in your example.
How should these be treated?
Sub Splitter()
Dim NumString As String
Dim TextString As String
Range("A1").Select
Selection.End(xlDown).Select
rr = ActiveCell.Row
For i = 2 To rr
NumString = ""
TextString = ""
TheString = Trim(Range("A" & i).Value)
TheLength = Len(TheString)
For j = 1 To TheLength
It = Mid(TheString, j, 1)
If IsNumeric(It) = True Then
NumString = NumString & It
Else
TextString = TextString & It
End If
Next j
Range("B" & i).Value = TextString
Range("C" & i).Value = NumString
Next i
End Sub
On Apr 1, 5:46 am, Harmeet Singh <[email protected]> wrote:
> Thanks Dave
> Its my pleasure to get appreciation from a legend (DAVE) :)
>
> God bless u
>
> Thanks & Regards,
>
> Harmeet Singh
>
> Sent via BlackBerry Wireless
>
> On Wed, Mar 31, 2010 at 7:52 AM, Dave Bonallack
> <[email protected]>wrote:
>
>
>
> > Hi Harmet,
> > MUCH shorter than mine! Very good.
> > Does need to be entered with Ctrl+Shift+Enter
> > Regards - Dave.
>
> > ------------------------------
> > From: [email protected]
> > Date: Wed, 31 Mar 2010 05:56:49 +0530
> > Subject: Re: $$Excel-Macros$$ Please help In seperating alphabets and
> > numbers from a cell
>
> > To: [email protected]
>
> > *found one more method.....*
> > *PFA file*
>
> > Thanks & Regards,
>
> > Harmeet Singh
>
> > Sent via BlackBerry Wireless
>
> > On Wed, Mar 31, 2010 at 5:29 AM, Dave Bonallack <[email protected]
> > > wrote:
>
> > Hi,
> > I've thought about it some more and have come up with a rediculously long
> > formula that does the job.
>
> > =MID(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),100),1,MIN(S
> > EARCH({"a","b","c","d","e","f","g","h","I","j","k","l","m","n","o","p","q",
> > "r","s","t","u","v","w","x","y","z"},MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9
> > },A1&"0123456789")),100)&"abcdefghijklmnopqrstuvwxyz"))-1)
>
> > This can be considerably shortened with one or two helper columns.
>
> > I've attached a workbook.
>
> > Regards - Dave.
>
> > ------------------------------
> > Date: Tue, 30 Mar 2010 13:55:06 +0530
> > Subject: $$Excel-Macros$$ Please help In seperating alphabets and numbers
> > from a cell
> > From: [email protected]
> > To: [email protected]
>
> > Dear All
>
> > I am seeking for the reply of one queryy
> > how to seperate alphats and no.
> > eg: jdsdnc2123asdd
> > iofruif68732fnvv
>
> > thnks in advance
>
> > regards
> > Abhidha
>
> > --
>
> > ---------------------------------------------------------------------------
> > -------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our Facebook Group @
> >http://www.facebook.com/group.php?gid=287779555678
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to [email protected]
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
> > To unsubscribe from this group, send email to excel-macros+
> > unsubscribegooglegroups.com or reply to this email with the words "REMOVE
> > ME" as the subject.
>
> > ------------------------------
> > Meet local singles online. Browse profiles for
> > FREE!<http://clk.atdmt.com/NMN/go/150855801/direct/01/>
>
> > --
> > ---------------------------------------------------------------------------
> > -------
>
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our Facebook Group @
> >http://www.facebook.com/group.php?gid=287779555678
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to [email protected]
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
> > --
>
> > ---------------------------------------------------------------------------
> > -------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our Facebook Group @
> >http://www.facebook.com/group.php?gid=287779555678
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to [email protected]
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
> > ------------------------------
> > Download a free gift for your PC. Get personal with
> > Windows.<http://experience.windows.com>
>
> > --
>
> > ---------------------------------------------------------------------------
> > -------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Join our Facebook Group @
> >http://www.facebook.com/group.php?gid=287779555678
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to [email protected]
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe, reply using "remove me" as the subject.
