Excel - How do I remove characters from th left side of a string

[mtmanecho]

I am just digging into excel and found that it would be very helpful if I could chop so many characters off of a certain cell or cells

example: "Date:" and "Request ID:" are static and unwanted

Date: 11/30/2002
Request ID: BrKUq43D

any ideas?

 

[mtmanecho]

I was able to get these two cells with
=RIGHT($A$151,10)

yet I am still stumped with a name field because I do not know how long the name is going to be when It gets pasted in.

example:
name: John Doe
&
name: John Dawson

any ideas how to just strip off of the left?

 

[Darrylles]

Hi mt,

Just as with the 'right' function, use the 'len' function to find length of string.

Sorry - I don't understand what you are attempting to 'strip off'.


Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[kylua]

Use the instr function to find the location of the colon or space:

thename = activecell.value 'or whatever to get the value
locale = instr(thename," &quot
surname = right(thename, len(thename)- locale + 1)

This won't work on "Cheryl Ann Smith" as it will return "Ann Smith". It will also be similarly confused by initials. You could loop it thru until locale = 0, (there are no spaces) and hope no-one has a double surname that they don't split with a dash:

thename = activecell.value 'or whatever to get the value
locale = instr(thename," &quot
do
surname = right(thename, len(thename)- locale + 1)
locale = instr(thename," &quot
loop until locale = 0

 

[RobBroekhuis]

If, as you say, the entries like "Date:" and "Request ID:" are static and you know there will always be in that place, then you only need:

MyDate=mid(range("A151&quot,7)
MyRequestID=mid(range("A152&quot,13)

That's if you're using VBA (which is what this forum typically discusses). By formula, use Darrylle's approach:

=right(A151,len(A151-6))
etc


Rob

 

[mtmanecho]

Thanks I will try these
you guys ROCK!!!

 

[RobBroekhuis]

Oops, just noticed a mistake in my post above. Make that formula
=right(A151,len(A151)-6) (i.e., shift the parenthesis around)
Rob

 

[mtmanecho]

This works perfect

=right(A151,len(A151)-6)

Now that I look at it, it makes total sense but for the life of me I couldn't grasp it. Thank you