Cutting rightmost characters

[stussy]

Hi

Hopefully simple for someone...we have a sheet where the values we want appear like this each day:

GBP 4.99
GBP 22.50
GBP 134.99

What I would like to do is strip the GBP and following space, so I am left with just the value - I can then use this properly as currency and sum the fields etc. I've messed around with various combinations of left, right and search, but am completely lost now!

Any ideas?!!!!

Cheers

Mike

 

[stussy]

Never mind...figured it out after a coffee! If anyone has a neater way than my blunderbuss, please let me know. I'm using len to get field length, search(" ",cellref) to get space character, len-space result to get number of useful characters, then right(cellref,useful characters) to return the bit I want. Horrible I'm sure, but this is a man that had to sit maths twice...

 

[AnotherHiggins]

Hey stussy,

If you want to strip off "GBP " (including the space), then you always want to strip off four characters. So there's no need to search. This should work:
[COLOR=blue white]=right(a1,len(a1)-4)[/color]

The only problem is that you are left with a text string that cannot be formatted as currency. To make it a number that you can format as currency, modify the formula to this:
[COLOR=blue white]=value(right(a1,len(a1)-4))[/color]

Now just format the cells as desired.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[TonyJollans]

I think MID is easier (and more efficient) than RIGHT for this ..

[blue][tt]=MID(A1,5,99)[/tt][/blue]

Note the 99 can be any number which is at least the length of the number you want to keep.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[earthandfire]

Tony

=MID(A1,5)

you don't need the length parameter


Hope this helps.

 

[GlennUK]

Hi earthandfire,

that's not true of Excel 97. You get an error if you try to use MID without the third argument. I haven't got access to any later versions at this moment in time, but thought I'd chip in my tuppence worth about Excel 97.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

You don't need the length parameter in the VBA function, but you do the Worksheet function.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[AnotherHiggins]

Many ways to skin the .

Mid will obviously work fine, but don't forget to wrap it in VALUE() to be able to format the results as currency.

earthandfire, Excel 2K doesn't like MID w/o the third argument either. But Tony already explained why.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[earthandfire]

Sorry about that - I just assumed it was the same as VBA.

I'll keep quiet next time.

 

[KenWright]

What's wrong with a simple Find and replace, Finding 'GBP ' and replacing with nothing.

Does it all in situ with no need for helper columns etc

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

Damn you and your sensible elegant answers, KenWright!


[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[stussy]

Jeesh you go away for a weekend and come back to all this! Thanks for all the responses, much tidier. I was also ready to come back and post questioning why I couldn't format the columns as currency...so you lot saw that one coming!

Final question for this then - the number of lines returned on each daily sheet is different - now I can total my values, is there any way to record a macro that will place that sum field under the last filled line/cell?

Thanks again

Mike