Home 
Search 
Today's Posts 
#1




Copy only letters, not numbers
I have a huge long column of textproduct codes, to be exact, that could
look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#2




Copy only letters, not numbers
Assuming your data is in cell A1:A100, in cell B1 put the following formula:
=LEFT(A1,IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7 ,8,9},A1&"0123456789")))1) but make sure you ARRAY ENTER it, by using CTRL+SHIFT+ENTER If successful you will see curly braces appear around the formula, eg {=formula} Now just copy and then paste across B2:B100 Regards Ken........................ "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#3




Copy only letters, not numbers
Thank you, Ken! That works wonderfully! What a pile of work that has saved
me! I know that Excel can do anything. I just wish I knew how to make it do anything! Thanks again. Connie "Ken Wright" wrote: Assuming your data is in cell A1:A100, in cell B1 put the following formula: =LEFT(A1,IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7 ,8,9},A1&"0123456789")))1) but make sure you ARRAY ENTER it, by using CTRL+SHIFT+ENTER If successful you will see curly braces appear around the formula, eg {=formula} Now just copy and then paste across B2:B100 Regards Ken........................ "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#4




Copy only letters, not numbers
LOL, you're very welcome :)
Regards Ken............... "Connie Martin" wrote in message ... Thank you, Ken! That works wonderfully! What a pile of work that has saved me! I know that Excel can do anything. I just wish I knew how to make it do anything! Thanks again. Connie "Ken Wright" wrote: Assuming your data is in cell A1:A100, in cell B1 put the following formula: =LEFT(A1,IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7 ,8,9},A1&"0123456789")))1) but make sure you ARRAY ENTER it, by using CTRL+SHIFT+ENTER If successful you will see curly braces appear around the formula, eg {=formula} Now just copy and then paste across B2:B100 Regards Ken........................ "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#5




Copy only letters, not numbers
I assume that your first code is in A2
You can use a function as described by Kevin Backmann copy and paste this into a module in the visual basic editor. press <alt<F11 to get there insert a new module and paste the lines below into the right hand window Function ExtractAlpha(varVal As Variant) As String Dim intLen As Integer Dim strVal As String Dim i As Integer Dim strChar As String intLen = Len(varVal) For i = 1 To intLen strChar = Mid$(varVal, i, 1) If Asc(strChar) = 65 And Asc(strChar) <= 90 Or _ Asc(strChar) = 97 And Asc(strChar) <= 122 Then strVal = strVal & strChar End If Next i ExtractAlpha = strVal End Function then enter =ExtractAlpha(A2) in B2 To get writ of the x's at the end You will have to use a normal if formula in C2 =IF(RIGHT(B2,1)="x",MID(B2,1,LEN(B2)1),B2) extend B2 and C2 as far as you need to by left click and drag down of the right hand bottom corners of the cells after the mouse pointer has changed to a "+" sign  Greetings from New Zealand "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#6




Copy only letters, not numbers
You really *don't* need an array entry for this formula Ken.
And this one will *not* return an error when the cell being referenced is empty: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))1)  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Ken Wright" wrote in message ... LOL, you're very welcome :) Regards Ken............... "Connie Martin" wrote in message ... Thank you, Ken! That works wonderfully! What a pile of work that has saved me! I know that Excel can do anything. I just wish I knew how to make it do anything! Thanks again. Connie "Ken Wright" wrote: Assuming your data is in cell A1:A100, in cell B1 put the following formula: =LEFT(A1,IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7 ,8,9},A1&"0123456789")))1) but make sure you ARRAY ENTER it, by using CTRL+SHIFT+ENTER If successful you will see curly braces appear around the formula, eg {=formula} Now just copy and then paste across B2:B100 Regards Ken........................ "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#7




Copy only letters, not numbers
Just thought you might find it interesting to know that this line...
If Asc(strChar) = 65 And Asc(strChar) <= 90 Or _ Asc(strChar) = 97 And Asc(strChar) <= 122 Then can be replaced with this one... If strChar Like "[AZaz]" Then  Rick (MVP  Excel) "Bill Kuunders" wrote in message ... I assume that your first code is in A2 You can use a function as described by Kevin Backmann copy and paste this into a module in the visual basic editor. press <alt<F11 to get there insert a new module and paste the lines below into the right hand window Function ExtractAlpha(varVal As Variant) As String Dim intLen As Integer Dim strVal As String Dim i As Integer Dim strChar As String intLen = Len(varVal) For i = 1 To intLen strChar = Mid$(varVal, i, 1) If Asc(strChar) = 65 And Asc(strChar) <= 90 Or _ Asc(strChar) = 97 And Asc(strChar) <= 122 Then strVal = strVal & strChar End If Next i ExtractAlpha = strVal End Function then enter =ExtractAlpha(A2) in B2 To get writ of the x's at the end You will have to use a normal if formula in C2 =IF(RIGHT(B2,1)="x",MID(B2,1,LEN(B2)1),B2) extend B2 and C2 as far as you need to by left click and drag down of the right hand bottom corners of the cells after the mouse pointer has changed to a "+" sign  Greetings from New Zealand "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#8




Copy only letters, not numbers
LOL  over to you for that one my friend  Should have checked it really.
Hope you are keeping well  Been a little while now :) Regards Ken................ "RagDyer" wrote in message ... You really *don't* need an array entry for this formula Ken. And this one will *not* return an error when the cell being referenced is empty: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))1)  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Ken Wright" wrote in message ... LOL, you're very welcome :) Regards Ken............... "Connie Martin" wrote in message ... Thank you, Ken! That works wonderfully! What a pile of work that has saved me! I know that Excel can do anything. I just wish I knew how to make it do anything! Thanks again. Connie "Ken Wright" wrote: Assuming your data is in cell A1:A100, in cell B1 put the following formula: =LEFT(A1,IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7 ,8,9},A1&"0123456789")))1) but make sure you ARRAY ENTER it, by using CTRL+SHIFT+ENTER If successful you will see curly braces appear around the formula, eg {=formula} Now just copy and then paste across B2:B100 Regards Ken........................ "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
#9




Copy only letters, not numbers
Yes, it's been a while.<g
I remember spending hours in these groups ... now it's minutes ... if at all, and mostly now ... it's not at all.  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Ken Wright" wrote in message ... LOL  over to you for that one my friend  Should have checked it really. Hope you are keeping well  Been a little while now :) Regards Ken................ "RagDyer" wrote in message ... You really *don't* need an array entry for this formula Ken. And this one will *not* return an error when the cell being referenced is empty: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))1)  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Ken Wright" wrote in message ... LOL, you're very welcome :) Regards Ken............... "Connie Martin" wrote in message ... Thank you, Ken! That works wonderfully! What a pile of work that has saved me! I know that Excel can do anything. I just wish I knew how to make it do anything! Thanks again. Connie "Ken Wright" wrote: Assuming your data is in cell A1:A100, in cell B1 put the following formula: =LEFT(A1,IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7 ,8,9},A1&"0123456789")))1) but make sure you ARRAY ENTER it, by using CTRL+SHIFT+ENTER If successful you will see curly braces appear around the formula, eg {=formula} Now just copy and then paste across B2:B100 Regards Ken........................ "Connie Martin" wrote in message ... I have a huge long column of textproduct codes, to be exact, that could look something like this: CNUB275X3 or PB24X275. What I need is a formula, which I will put in the column beside this one, that will look at the code and put only the letters up to the first number. So, in this case it would put CNUB in the first row's cell and then PB for the next one. Is this possible? Connie 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Copy first few letters from a cell  Excel Worksheet Functions  
Sorting  cells containing numbers, numbers and letters  Excel Discussion (Misc queries)  
Copy the first two or three letters of a cell to another one  Excel Discussion (Misc queries)  
create selfgenerating numbers with letters and numbers  Excel Discussion (Misc queries)  
Letters = Numbers  Charts and Charting in Excel 