Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Un - Concatenate ?
Greetings all,
I'm new and inexperienced. My problem - to unconcatenate - to split a string into its leftmost character and the remainder (which may be nothing). I guess I can strip the leftmost alpha/numeric character from the string using the LEFT function. What is the best expression for my remainder? Thanks in advance, Rednelle |
#2
|
|||
|
|||
Hi
Try Data|Text to columns If you use Fixed Width and click after the first character in the little window, it will split it for you. Save a copy before you start! -- Andy. "Rednelle" wrote in message ... Greetings all, I'm new and inexperienced. My problem - to unconcatenate - to split a string into its leftmost character and the remainder (which may be nothing). I guess I can strip the leftmost alpha/numeric character from the string using the LEFT function. What is the best expression for my remainder? Thanks in advance, Rednelle |
#3
|
|||
|
|||
Hi Rednelle,
=RIGHT(A1,LEN(A1)-1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rednelle" wrote in message ... Greetings all, I'm new and inexperienced. My problem - to unconcatenate - to split a string into its leftmost character and the remainder (which may be nothing). I guess I can strip the leftmost alpha/numeric character from the string using the LEFT function. What is the best expression for my remainder? Thanks in advance, Rednelle |
#4
|
|||
|
|||
Niek Otten wrote...
Hi Rednelle, =RIGHT(A1,LEN(A1)-1) .... When the position at which to break strings is measured from the beginning or left side of the string, RIGHT isn't the best choice. =MID(A1,2,1E9) |
#5
|
|||
|
|||
Thanks, Niek; and thanks, Harlan
I'll try both. Rednelle "Harlan Grove" wrote in message oups.com... Niek Otten wrote... Hi Rednelle, =RIGHT(A1,LEN(A1)-1) ... When the position at which to break strings is measured from the beginning or left side of the string, RIGHT isn't the best choice. =MID(A1,2,1E9) |
#6
|
|||
|
|||
OK, both solutions work fine (well of course they would).
I figure 1E9 gives 10 to power 9. And I understand basic expression syntax =( ? ,this,else that) Just for my interest, Harlan, can you please explain what your expression is doing / why it works ? Rednelle "Harlan Grove" wrote in message oups.com... Niek Otten wrote... Hi Rednelle, =RIGHT(A1,LEN(A1)-1) ... When the position at which to break strings is measured from the beginning or left side of the string, RIGHT isn't the best choice. =MID(A1,2,1E9) |
#7
|
|||
|
|||
Rednelle wrote...
.... Just for my interest, Harlan, can you please explain what your expression is doing / why it works ? .... MID(x,2,SomethingLARGE) MID extracts a substring from the string x. The 2nd argument is the starting position for the substring measured from the beginning/left of x. In your case, that's 2 for the 2nd char position. The 3rd argument is the length of the substring, but if it's larger than the remaining length of the string starting at the 2nd argument, it's effectively capped. So if SomethingLARGE is greater than LEN(x), the MID call will return the rest of x beginning at char position 2. FWIW, VBA's Mid function doesn't require a 3rd argument to return the remainder of the string. |
#8
|
|||
|
|||
Hi Harlan,
<RIGHT isn't the best choice Is it right or isn't it? Please elaborate -- Kind Regards, Niek Otten Microsoft MVP - Excel "Harlan Grove" wrote in message oups.com... Niek Otten wrote... Hi Rednelle, =RIGHT(A1,LEN(A1)-1) ... When the position at which to break strings is measured from the beginning or left side of the string, RIGHT isn't the best choice. =MID(A1,2,1E9) |
#9
|
|||
|
|||
Niek Otten wrote...
<RIGHT isn't the best choice Is it right or isn't it? Please elaborate .... RIGHT(x,LEN(x)-(p-1)) works, extracting the rightmost substring from x beginning at char position p measured from the beginning of the string. However, it's easier just to use MID(x,p,N), where N is very large (any positve value that can be held in a long integer will work). It's easier still in VBA, in which it'd just be MID(x,p). There are other ways to do this. =SUBSTITUTE(x,LEFT(x,p-1),"",1) =REPLACE(x,1,p-1,"") RIGHT may have a place, but it's not built in to many programming languages because it's seldom needed. IMO, it only makes sense to use it when the substring's starting position is relative to the end/right of the string directly rather than as derived from a beginning/left of string position. |
#10
|
|||
|
|||
I follow; thanks again, guys.
Rednelle "Harlan Grove" wrote in message oups.com... Niek Otten wrote... <RIGHT isn't the best choice Is it right or isn't it? Please elaborate ... RIGHT(x,LEN(x)-(p-1)) works, extracting the rightmost substring from x beginning at char position p measured from the beginning of the string. However, it's easier just to use MID(x,p,N), where N is very large (any positve value that can be held in a long integer will work). It's easier still in VBA, in which it'd just be MID(x,p). There are other ways to do this. =SUBSTITUTE(x,LEFT(x,p-1),"",1) =REPLACE(x,1,p-1,"") RIGHT may have a place, but it's not built in to many programming languages because it's seldom needed. IMO, it only makes sense to use it when the substring's starting position is relative to the end/right of the string directly rather than as derived from a beginning/left of string position. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I concatenate information in Excel and keep the number form | Excel Worksheet Functions | |||
space between text strings with concatenate | Excel Discussion (Misc queries) | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
Concatenate two halves of a phone number | Excel Worksheet Functions |