If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Looking for a Function? similar to LEFT
I have a list of org unit names that are always preceded by a 3 digit number.
How can I just show the text and not the numbers? Example: 001 Org Unit Name *note* there is also always a space after the 3 digits. In this example I only want a column to display "Org Unit Name." Help? KR P.S. I looked through other posts and did not see anything readily similar. |
#2
|
|||
|
|||
Looking for a Function? similar to LEFT
=RIGHT(A1,LEN(A1)-4)
-- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "KatieR" wrote: I have a list of org unit names that are always preceded by a 3 digit number. How can I just show the text and not the numbers? Example: 001 Org Unit Name *note* there is also always a space after the 3 digits. In this example I only want a column to display "Org Unit Name." Help? KR P.S. I looked through other posts and did not see anything readily similar. |
#3
|
|||
|
|||
Looking for a Function? similar to LEFT
Try looking up the mid() function in Excel Help. Assuming your original unit
name with numbers is in A1: =MID(A1,5,20). Adjust the 20 to match the longest possible unit name. Here is the syntax: =MID(text,start_num,num_chars). OR You could also use the text-to-columns feature and designate your data in the Text to Columns Wizard as space delimited. Hope this helps. rzink "KatieR" wrote: I have a list of org unit names that are always preceded by a 3 digit number. How can I just show the text and not the numbers? Example: 001 Org Unit Name *note* there is also always a space after the 3 digits. In this example I only want a column to display "Org Unit Name." Help? KR P.S. I looked through other posts and did not see anything readily similar. |
#4
|
|||
|
|||
Looking for a Function? similar to LEFT
Several ways
=right(a2,len(a2)-4) -- Don Guillett Microsoft MVP Excel SalesAid Software "KatieR" wrote in message ... I have a list of org unit names that are always preceded by a 3 digit number. How can I just show the text and not the numbers? Example: 001 Org Unit Name *note* there is also always a space after the 3 digits. In this example I only want a column to display "Org Unit Name." Help? KR P.S. I looked through other posts and did not see anything readily similar. |
#5
|
|||
|
|||
Looking for a Function? similar to LEFT
Another way is
=MID(A1,FIND(" ",A1)+1,999) Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Tue, 16 Mar 2010 09:21:01 -0700, KatieR wrote: I have a list of org unit names that are always preceded by a 3 digit number. How can I just show the text and not the numbers? Example: 001 Org Unit Name *note* there is also always a space after the 3 digits. In this example I only want a column to display "Org Unit Name." Help? KR P.S. I looked through other posts and did not see anything readily similar. |
#6
|
|||
|
|||
Looking for a Function? similar to LEFT
Chip Pearson wrote...
Another way is =MID(A1,FIND(" ",A1)+1,999) .... Good to use the delimiter. Possible to dispense with the 999. =REPLACE(A1,1,FIND(" ",A1),"") |
Thread Tools | |
Display Modes | |
|
|