Cutting of data from cell

[Poweruser99]

Hello all.

I have to do a bit of data cleaning.
Below is sample data.

CANON INC.CANON DIGITAL CAMERA SUPPORT LIBRARY V1.0.0.1
CANON INC.CANON DIGITAL CAMERA SUPPORT LIBRARY V3.6.0.0
CANON INC.CANON DIGITAL CAMERA SUPPORT LIBRARY V4.5.0.0
CANON INC.CANON DIGITAL CAMERA V1.0.0.19
CANON INC.CANON DIGITAL CAMERA V1.1.0.11
CANON INC.CANON DIGITAL CAMERA V1.2.0.13
CANON INC.CANON DIGITAL CAMERA V5.0.0.15

From the right hand side I want to remove everything up until the V and what ever number comes with it. Below is what I want in the final result.

CANON INC.CANON DIGITAL CAMERA SUPPORT LIBRARY V1
CANON INC.CANON DIGITAL CAMERA SUPPORT LIBRARY V3
CANON INC.CANON DIGITAL CAMERA SUPPORT LIBRARY V4
CANON INC.CANON DIGITAL CAMERA V1
CANON INC.CANON DIGITAL CAMERA V1
CANON INC.CANON DIGITAL CAMERA V1
CANON INC.CANON DIGITAL CAMERA V5

Can someone guide me with a formula or macro to write.

 

[AnotherHiggins]

Try this:
[COLOR=blue white]=left(a1, search("V",a1,1)+1)[/color]

[tt]_____
[blue]-John[/blue][/tt]

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

 

[SkipVought]

If there's not other V in any data, you could alos use Data/text to columns... and use V as the delimiter and select do not import column (skip) for the data you don't want.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[Poweruser99]

Thanks this sort of works but not for the long fields such as

CHARON SYSTEMS INC.CHARON UNIPRINT SERVER V2.10.0892

 

[Poweruser99]

No there will be Vs in other parts of the data.

 

[Poweruser99]

Isnt there any way I could start the search from the Right, that way there is a guarantee that only one V will show up

 

[xlbo]

only using VBA code......if you are happy to do that then please post in the VBA forum (Forum707)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[KenWright]

If the format at the end is ALWAYS the same, ie V?.?.?.? (Don't care about the size of the numbers, just the number of dots at the end) then try this:-

=LEFT(A1,FIND("[",SUBSTITUTE(A1,".","[",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-2))-1)

Regards
Ken..............

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

 

[KenWright]

Should have said - also assumes you don't have any instances of [ in your data. If you do then I can find you a character that you don't I expect.

Regards
Ken...........

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

 

[Gavona]

not sure why Ken didn't use his approach more directly:
=LEFT(A1,FIND("[",SUBSTITUTE(A1,"V","[",LEN(A1)-LEN(SUBSTITUTE(A1,"V",""))))+1)



Gavin

 

[KenWright]

Would have been easier actually, and had I taken a close look at the last example the OP posted, I'd also have seen that my assumptions about the end format didn't hold good for all his data, as he only has a couple of dots at the end of that one.

Your adaptation though overcomes that limitation

Regards
Ken...........

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