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 code or macro to write.

 

[SkipVought]

Hey PU,

Why didn't you post the example that caused you to get an answer from the VBA forum rather than the OFFICE forum?

BTW, it's NOT because it's a "LONG" field!

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

 

[Poweruser99]

I figured out that its not because of a long field, rather its because of multiple occurances of "V

 

[PHV]

As we are in the VBA forum, have a look at the InStrRev function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

...and with the "multiple occurances of 'V'", comes PHV's suggestion.

Otherwise, someone would have suggested the InStr function, and we'd be back a square ONE.

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

 

[Poweruser99]

Thanks for the feedback. I'm working on it now and will let you know the results.

 

[bpeirson]

I copied your list into Excel starting at cell A1.

I put this formula in cell B1 and copied it into the required number of rows.

=LEFT(A1,SEARCH(".",A1,SEARCH("Camera",A1))-1)

The formula will only show the text left of the first decimal point it finds after the word camera.