Just having a fiddle with this. It is possible to do it with formulas,
although it's painful.
Assuming your value is in A1 and that it will never contain an equals sign
...
=LEFT(A1,FIND("=",SUBSTITUTE(A1," ","=",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-1)
=VALUE(MID(A1,FIND("=",SUBSTITUTE(A1," ","=",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,99))
The second SUBSTITUTE() removes all spaces, allowing the number of spaces in
the string to be counted by comparing the new length.
The first SUBSTITUTE() replaces the last space with an equals sign, so that
the FIND() can find it and break the string.
All this because there is no "find backwards" in the built-in Excel
functions (2003 anyway).
So, a little bit of VBA gets you much simpler formula.
Option Explicit
Public Function FindLast(ToFind As String, InText As String) As Integer
FindLast = InStrRev(InText, ToFind)
End Function
and the formulas then simplify to
=LEFT(A1,FindLast(" ",A1)-1)
=VALUE(MID(A1,FindLast(" ",A1)+1,99))
Of course, you could do the lot with VBA, but there's not much gain, and
you'll find FindLast() useful at other times too.
Regards, Dave S
--- In ExcelVBA@yahoogroups.com, Barry White <imtigerwords@...> wrote:
>
> 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: 2092/3985 - Release Date: 10/30/11
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