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>...
Senin, 31 Oktober 2011
Re: [ExcelVBA] Re: Text to columns, alpha characters first then numeric
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...
[ExcelVBA] Re: Text to columns, alpha characters first then numeric
Two ideas: - Parse it as a string variable, which it will be, [mid(String,n,1) in a (n) loop] until you get a digit, then do your split with something like LEFT(String,LEN(s)-nn)and RIGHT(String,LEN(s)-nn). - If you know the number is always last, then parse it for a space from the right. Your results example doesn't work on Yahoo. It doesn't show tabs, but no problem. -- 73, Steve --- 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...
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...
[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] __._,_.___ ...
[belajar-excel] Digest Number 1382
Milis Belajar Microsoft Excel Messages In This Digest (15 Messages) 1a. Bls: [belajar-excel] Menyisipkan data di kolom sebelah From: Amar luqman Hakim 1b. Re: Bls: [belajar-excel] Menyisipkan data di kolom sebelah From: Luqman Elhakim 1c. Re: Menyisipkan data di kolom sebelah From: Haps 1d. Re: Menyisipkan data di kolom sebelah From: Kid Mr. 2a. Re: lockup dan formula.xls From: stefen ...
Langganan:
Postingan (Atom)