Home 
Search 
Today's Posts 
#1




Trim Function
Hello and thanks in advance 
I have a column of room numbers with [alphas] mixed in as seen below. A 100 101D 103F 104 1004E Note that the numeric values are sometimes in the thousand range. I need to create a column B where the numeric value of the room number will be split up from column C, the alpha, as below; A B C 100 100 101D 101 D 103F 103 F 104 104 1004E 1004 E Matt 
#2




Trim Function
Assuming the Alpha is always one character and always either absent or located on the right...here you go: If you value is in Column A, place this in ColumnB (strip the digits): +VALUE(IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)1))) Place this in Column C (snag the character): +IF(ISNUMBER(A1),"",RIGHT(A1,1)) This can be modified even more for situations in which the letter appears at the beginning or if there are two letters; however, I didn't want to make the forumla unduely complicated on the first run.  Lotus123  Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611 View this thread: http://www.excelforum.com/showthread...hreadid=487007 
#3




Trim Function
If there is always only one letter as the alpha, then the following formulas
will work: [b1]=IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)1)) [c1]=IF(ISNUMBER(A1),"",RIGHT(A1,1)) However, if any of your values have spaces at the end, then the data will have to be trimmed first  simply trim(a1) and then adjust the other formulas to link to the trimmed version of the data column. If the alpha can be more than one character then it can probably still be done but more info will be needed on the rules that the alphas follow...i.e when is there more than one character, etc. hth, Dave "Matt M HMS" wrote: Hello and thanks in advance  I have a column of room numbers with [alphas] mixed in as seen below. A 100 101D 103F 104 1004E Note that the numeric values are sometimes in the thousand range. I need to create a column B where the numeric value of the room number will be split up from column C, the alpha, as below; A B C 100 100 101D 101 D 103F 103 F 104 104 1004E 1004 E Matt 
#4




Trim Function
In B1, use
=LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),ROW(IND IRECT("A1:A"&LEN(A1))),255))1) which is an array formula, so commit with CtrlShiftEnter In C1, =SUBSTITUTE(A1,B1,"") and copy doen  HTH RP (remove nothere from the email address if mailing direct) "Matt M HMS" <Matt M wrote in message ... Hello and thanks in advance  I have a column of room numbers with [alphas] mixed in as seen below. A 100 101D 103F 104 1004E Note that the numeric values are sometimes in the thousand range. I need to create a column B where the numeric value of the room number will be split up from column C, the alpha, as below; A B C 100 100 101D 101 D 103F 103 F 104 104 1004E 1004 E Matt 
#5




Trim Function
=SUBSTITUTE(A2,C,"")
assuming you have the valuse in C, if you want to get the numbers without using column C =IF(ISERR((A1)),LEFT(A1,MATCH(FALSE,ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)),0)1),A1) entered with ctrl + shift & enter assuming all numbers are to the left of the letter  Regards, Peo Sjoblom "Matt M HMS" <Matt M wrote in message ... Hello and thanks in advance  I have a column of room numbers with [alphas] mixed in as seen below. A 100 101D 103F 104 1004E Note that the numeric values are sometimes in the thousand range. I need to create a column B where the numeric value of the room number will be split up from column C, the alpha, as below; A B C 100 100 101D 101 D 103F 103 F 104 104 1004E 1004 E Matt 
#6




Trim Function
BTW, mine is agnostic to how many letters or numbers are in the string, even
0  HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... In B1, use =LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,ROW(INDIRECT("A1 :A"&LEN(A1))),1))),ROW(IND IRECT("A1:A"&LEN(A1))),255))1) which is an array formula, so commit with CtrlShiftEnter In C1, =SUBSTITUTE(A1,B1,"") and copy doen  HTH RP (remove nothere from the email address if mailing direct) "Matt M HMS" <Matt M wrote in message ... Hello and thanks in advance  I have a column of room numbers with [alphas] mixed in as seen below. A 100 101D 103F 104 1004E Note that the numeric values are sometimes in the thousand range. I need to create a column B where the numeric value of the room number will be split up from column C, the alpha, as below; A B C 100 100 101D 101 D 103F 103 F 104 104 1004E 1004 E Matt 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Date & Time  New Users to Excel  
Hyperlinks using R[1]C[1] and offset function in its cell referenc  Excel Worksheet Functions  
Conversion  Excel Worksheet Functions  
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE.  Excel Worksheet Functions  
Find a Function to use accross different worksheets  Excel Worksheet Functions 