Hi All
Here is a way to do it using VBA :-
Public Function DivideCell(vCellToDivide As Variant, Optional vMode As Variant = 1) As Variant
Application.Volatile
Dim vSplitArray As Variant, vInputString As Variant, vLeftPart As Variant, vRightPart As Variant
'
vInputString = Trim(vCellToDivide.Value)
vSplitArray = Split(vInputString, " ")
vRightPart = vSplitArray(UBound(vSplitArray))
vLeftPart = Left(vInputString, Len(vInputString) - Len(vRightPart))
If vMode = 1 Then
DivideCell = vLeftPart
Else
DivideCell = vRightPart
End If
End Function
To get the left part the function is called like :- =DivideCell( A5) and to get the right part you use :- =DivideCell( A5,2)
Doing it this way gives you the opportunity to validate the inputs and test the result strings, which you ought to do if you are using this a lot in a business environment.
One problem you may have to look out for is the invisible HTML Ascii 160 character which will fool the function. Use the Replace function in the first line.
Regards
Del +++
>________________________________
>From: Barry White <imtigerwords@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Monday, 31 October 2011, 22:54
>Subject: Re: [ExcelVBA] Text to columns, alpha characters first then numeric
>
>
>
>Yes, the numeric part is always at the end of the text.
>Yes, the numeric part never has any spaces in it
>
>I would like the numeric part separated as a number, not text.
>
>Eric Lutz
>
>________________________________
>From: David Smart <smartware.consulting@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Monday, October 31, 2011 3:24 PM
>Subject: Re: [ExcelVBA] Text to columns, alpha characters first then numeric
>
>
>
>Please confirm:
>
>1) The numeric part is always at the end of the text
>2) The numeric part never has any spaces in it.
>
>Also, do you want the numeric part separated as a number or as a string
>value?
>
>Regards, Dave S
>
>----- Original Message -----
>From: "Barry White" <imtigerwords@yahoo.com>
>To: <ExcelVBA@yahoogroups.com>
>Sent: Tuesday, November 01, 2011 5:51 AM
>Subject: [ExcelVBA] Text to columns, alpha characters first then numeric
>
>Howdy all,
>
>I have example data below,
>
>DIFF WKND PCT A 1090.52
>REG 13621.58
>
>I want the above data split into 2 columns and only ever two columns.
>
>1. All the text or alpha characters in one column
>
>2. All the numeric characters in another column
>
>The result should be below:
>
>DIFF WKND PCT A 1090.52
>REG 13621.58
>
>How do I do this? Text to columns by the "delimited space" method winds up
>that the first row contains 5 columns and the second row contains two, and
>the numeric portion is slotted in different columns because of it.
>Eric Lutz
>
>.
>
>[Non-text portions of this message have been removed]
>
>------------------------------------
>
>----------------------------------
>Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
>here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
>ARCHIVES for EXCEL VBA.
>
>----------------------------------
>Visit our ExcelVBA group home page for more info and support files:
>http://groups.yahoo.com/group/ExcelVBA
>
>----------------------------------
>More free tutorials and resources available at:
>http://www.mousetrax.com
>
>----------------------------------Yahoo! Groups Links
>
>-----
>No virus found in this message.
>Checked by AVG - www.avg.com
>Version: 10.0.1411 / Virus Database: 1522/3985 - Release Date: 10/30/11
>
>[Non-text portions of this message have been removed]
>
>
>
>
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar