new to vba forum looking to search and replace

[oracle765]

Hi Professionals.

I am new to vba programming as I am originally from an Oracle background.

anyway I have a number of very large spreadsheets which have a column with version numbers from various vendors, what I am looking to do is enable my work colleagues to just click a button and ammend the versions by looking for the first . and replace something like this

version numbers raw
11.3.2.1
10.5.2.1
10.4.1.1
401.3.2
null
unknown

amended
11.x
10.x
10.x
401.x
version unknown
version unknown

can this be done via a button click

thanks all

[Richard Buttrey]

Hi,

Do you really need a macro. You could keep a column which reads the version and converts it. e.g.

Please Login or Register  to view this content.

Or if you do need a macro just have the macro create the formula in a spare helper column, copy the column and paste it back as values to the original column then delete the helper column.

[oracle765]

Hi Richard

Thanks for that like I said i am totally new to this, I have over 42000 column values that need converting could you explain to me what you mean exactly by keep a column I have pasted the following formula at the bottom of row b after the values but nothing is happening

=IFERROR(LEFT(B1,FIND(".",B1)-1)&".x","version unknown")

[oracle765]

Hi again Richard

I have figured out how to put the formula in but it does not work properly. If I have a column value with just version 6 or 7 it shows up as "version unknown"

please advise

thanks
Alan

[Richard Buttrey]

Hi,

Your original post implied all versions had a . separating elements of the version number. When posting questions it's important to mention all relevant factors and this should include all permutations of data. My answer was based on the fact that all versions proper had a . in them.

Maybe

Please Login or Register  to view this content.

but that assumes a single version digit is a number and not text that just happens to look like a number.

[oracle765]

thanks richard sorry for not explaining properly

[oracle765]

yes it could be a text value that looks like a number

[Richard Buttrey]

Hi,

We need to know whether it could be either. i.e. either text OR a number in order to cover all bases.

[oracle765]

Hi again


it could be a number or it could be text that looks like a number


thanks