Un - Concatenate ?

  • Thread starter Thread starter Rednelle
  • Start date Start date
R

Rednelle

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
 
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!
 
Niek Otten wrote...
Hi Rednelle,

=RIGHT(A1,LEN(A1)-1)
Click to expand...
....

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)
 
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
 
Rednelle wrote...
....
Just for my interest, Harlan, can you please explain what your expression is
doing / why it works ?
Click to expand...
....

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.
 
Hi Harlan,

<RIGHT isn't the best choice>

Is it right or isn't it? Please elaborate

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Niek Otten wrote...
<RIGHT isn't the best choice>

Is it right or isn't it? Please elaborate
Click to expand...
....

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.
 
I follow; thanks again, guys.

Rednelle


Harlan Grove said:
Niek Otten wrote...
...

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.
Click to expand...
 
Back
Top