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 |
#21
|
|||
|
|||
Alphanumeric
Possible improvement...
In your sample data all the number strings are the same length. So, if that's the case then we can use much simpler formulas. In B2: =LEFT(A2,LEN(A2)-5) In C2: =RIGHT(A2,5) (returns a TEXT number) =--RIGHT(A2,5) (returns a NUMERIC number) The same condition applies to leading 0s. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Based on your posted sample...assuming the data starts in A2. Enter this formula in B2: =SUBSTITUTE(A2,C2,"") Note that this will return a TEXT value. If you want the number to be a numeric number then try this: =--SUBSTITUTE(A2,C2,"") However, if your number string starts with leading 0s this will strip them off. If you number strings are all the same length (as in your sample data) and will have leading 0s then you can use the "--" version and then custom number format to retain the leading 0s. Enter this formula in C2: =LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")))) Select both B2 and C2 and copy down as needed. -- Biff Microsoft Excel MVP "beejay" wrote in message ... Hi there Mike with regard to this alphanumeric problem, i have my own version of problem let say i have one colimn that contain: column A p12345 tr58967 drt45862 and i want to seperate the numbers and letters and will look like this column A column B column C p12345 p 12345 tr58967 tr 58964 drt45862 drt 45862 what type of function or formula i can use to do this task |
#22
|
|||
|
|||
Alphanumeric
thank you for the information
it works "T. Valko" wrote: Possible improvement... In your sample data all the number strings are the same length. So, if that's the case then we can use much simpler formulas. In B2: =LEFT(A2,LEN(A2)-5) In C2: =RIGHT(A2,5) (returns a TEXT number) =--RIGHT(A2,5) (returns a NUMERIC number) The same condition applies to leading 0s. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Based on your posted sample...assuming the data starts in A2. Enter this formula in B2: =SUBSTITUTE(A2,C2,"") Note that this will return a TEXT value. If you want the number to be a numeric number then try this: =--SUBSTITUTE(A2,C2,"") However, if your number string starts with leading 0s this will strip them off. If you number strings are all the same length (as in your sample data) and will have leading 0s then you can use the "--" version and then custom number format to retain the leading 0s. Enter this formula in C2: =LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")))) Select both B2 and C2 and copy down as needed. -- Biff Microsoft Excel MVP "beejay" wrote in message ... Hi there Mike with regard to this alphanumeric problem, i have my own version of problem let say i have one colimn that contain: column A p12345 tr58967 drt45862 and i want to seperate the numbers and letters and will look like this column A column B column C p12345 p 12345 tr58967 tr 58964 drt45862 drt 45862 what type of function or formula i can use to do this task |
#23
|
|||
|
|||
Alphanumeric
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "beejay" wrote in message news thank you for the information it works "T. Valko" wrote: Possible improvement... In your sample data all the number strings are the same length. So, if that's the case then we can use much simpler formulas. In B2: =LEFT(A2,LEN(A2)-5) In C2: =RIGHT(A2,5) (returns a TEXT number) =--RIGHT(A2,5) (returns a NUMERIC number) The same condition applies to leading 0s. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Based on your posted sample...assuming the data starts in A2. Enter this formula in B2: =SUBSTITUTE(A2,C2,"") Note that this will return a TEXT value. If you want the number to be a numeric number then try this: =--SUBSTITUTE(A2,C2,"") However, if your number string starts with leading 0s this will strip them off. If you number strings are all the same length (as in your sample data) and will have leading 0s then you can use the "--" version and then custom number format to retain the leading 0s. Enter this formula in C2: =LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"")))) Select both B2 and C2 and copy down as needed. -- Biff Microsoft Excel MVP "beejay" wrote in message ... Hi there Mike with regard to this alphanumeric problem, i have my own version of problem let say i have one colimn that contain: column A p12345 tr58967 drt45862 and i want to seperate the numbers and letters and will look like this column A column B column C p12345 p 12345 tr58967 tr 58964 drt45862 drt 45862 what type of function or formula i can use to do this task |
Thread Tools | |
Display Modes | |
|
|