Renaming cell contents

  • Thread starter Thread starter Jonathan Keller
  • Start date Start date
J

Jonathan Keller

I am trying to figure out if there is a way to automatically switch
certain cell input into an email address. For example:

The source data would be a row of cells containing

295
295t
354r
532

Could you somehow make those change into:

[email protected]
[email protected]
[email protected]
[email protected]

There would be many difference rules for the different numbers and
numbers with t and r, but if i could get a start in the right
direction on how to do this it would be appreciated.
 
With many different rules it may be difficult, but for any cells with numbers
only..................

Assuming 595 is in A1

In B1 enter =HYPERLINK(IF(ISNUMBER(A1),"store"&A1&"@...com"))

Best to group all cells with just numbers together so formula can be dragged.

Assuming 295t in A2

In B2 enter =HYPERLINK(IF(RIGHT(A2,1)="t","tireshop"&A2&"@...com"))

From these two you should be able to see a pattern to "get you started"

I would also assume you would insert something other than "..." for a path


Gord Dibben MS Excel MVP
 
I am trying to figure out if there is a way to automatically switch
certain cell input into an email address. For example:

The source data would be a row of cells containing

295
295t
354r
532

Could you somehow make those change into:

[email protected]
[email protected]
[email protected]
[email protected]

There would be many difference rules for the different numbers and
numbers with t and r, but if i could get a start in the right
direction on how to do this it would be appreciated.
Click to expand...

Gord's suggestion involves separate formulas, chosen manually.

There are at least three other ways to deal with this:

1) If you want to make a change programmatically (you did post to a
programming group), you can certainly program as many rules as may
exist through a worksheet change event. Then, after you type: 295t,
for example, into a cell and hit enter, the programming will
automatically modify it for you. Is that what you want? If so, I'll
post the code.

2) If you aren't going to be typing that information (that is, it
already exists in a worksheet) you can do the same thing you would do
in (1) in a macro which you could invoke whenever you see fit. Very
similar code.

3) An alternative to Gord's suggestion is to put a table somewhere
with the stubs that you want to use and then modify the formula
accordingly. For example, you might have a table that looks like
this:

Column
AA AB
r rest
t tireshop

And then a formula would look something like this:

=HYPERLINK(IF(ISNUMBER(A1),"store"&A1&"@...com",VLOOKUP(RIGHT(A1,1),$AA$1:$AB$2,2)&LEFT(A1,LEN(A1)-1)&"@...com"))

Put that formula in B1 and copy it down as far as you have data in A.
 
I'm looking more at option 2 as the data is imported from another
spreadsheet. Thank you so much for your help. I am trying to start
learning VBA programming to streamline processes, but its a very
daunting start.
 
Once you import the data, though, aren't you free to add a new column
and then the formula I already posted? That would be the easiest
method, by far.

If not, can you describe the data a bit more precisely? You used the
description: "a row of cells" and I would have expected it to be "a
column of cells".

If the information really is all in a row, then the macro will look
much different.

So, when you import the data, which cells, exactly, do you want to
change?
 
Once you import the data, though, aren't you free to add a new column
and then the formula I already posted?  That would be the easiest
method, by far.

If not, can you describe the data a bit more precisely?  You used the
description: "a row of cells" and I would have expected it to be "a
column of cells".

If the information really is all in a row, then the macro will look
much different.

So, when you import the data, which cells, exactly, do you want to
change?
Click to expand...

It is actually row A the data is in. I can give you all the different
scenarios for rules so you have an idea what the finished product
should look like.

any number from 1-399 that does not contain r or t should add
store#@.....com
any number with an r should say rest#@.....com
any number with a t should say tireshop#@.....com
any number from 400-599 (with the exception of 26 numbers in that
range) needs 200 subtracted from it and then have rest#@......com
 
Jonathan Keller said:
<...> I am trying to start
learning VBA programming to streamline processes, but its a very
daunting start.
Click to expand...


One way to start learning VBA is to record macros and then examine the
generated code ... and consult the built-in help frequently! My
experience with learning to use VBA indicates a learning curve spread
over many months, with gradually, but constantly, increasing benefits
realized from the effort invested.

Then, of course, come back here with questions where there are a number
of volunteers glad to help newcomers learn their way around! I see that
you posted from Google Groups -- there is a wealth of information in the
archives; I found that there is also a learning curve associated with
learning how to get value out of using the search engine, but I would
encourage you to spend some time there as well.

Welcome, and enjoy the process!!
 
One more attempt at lucidity. You now say that everything is in "row
A". "A" is a column, not a row. So, is everything in Column A? I'm
going to assume yes.

Is there a good reason why you can't just insert a new worksheet with
the table of 4 items previously identified and a column (column B)
after the data has been imported, put the appropriate formula in cell
B1 and then copy it down?

I strongly believe that anything that CAN be done in a worksheet, with
formulas, is better done in a worksheet than by creating a macro or
with VBA.

Assuming it is column A you are dealing with, the following formula
will do what you want (assuming you can insert the table somewhere):

B1=HYPERLINK(IF(ISNUMBER(A1),"store"&IF((A1>399)*(A1<600),A1-200,A1)&"@...com",
VLOOKUP(RIGHT(A1,1),$AA$1:$AB$2,2)&LEFT(A1,LEN(A1)-1)&"@...com"))

If you don't want to do the table, since you only have two letters,
you can do the following, instead (watch word wrap):

=HYPERLINK(IF(ISNUMBER(A1),"store"&IF((A1>399)*(A1<599),A1-200,A1)&"@...com",
IF(RIGHT(A1,1)="t","tireshop",IF(RIGHT(A1,1)="r","rest","UNDEFINED"))&LEFT(A1,LEN(A1)-1)&"@...com"))

If you precisely define those 26 exceptions I can see whether the
formula can be expanded to handle them economically.
 
Sorry, gave you the not yet finalized version. Here's one that works
as you described.

=HYPERLINK(IF(ISNUMBER(A1),IF(A1<400,"store"&A1,IF((A1>399)*(A1<600),"rest"&A1-200,"store"&A1))&"@...com",
IF(RIGHT(A1,1)="t","tireshop",IF(RIGHT(A1,1)="r","rest","UNDEFINED"))&LEFT(A1,LEN(A1)-1)&"@...com"))

Not only do you need to describe the 26 exceptions, you need to define
how the field is supposed to be built for those 26 exceptions.
 
Sorry, gave you the not yet finalized version.  Here's one that works
as you described.

=HYPERLINK(IF(ISNUMBER(A1),IF(A1<400,"store"&A1,IF((A1>399)*(A1<600),"rest"&A1-200,"store"&A1))&"@...com",
IF(RIGHT(A1,1)="t","tireshop",IF(RIGHT(A1,1)="r","rest","UNDEFINED"))&LEFT(A1,LEN(A1)-1)&"@...com"))

Not only do you need to describe the 26 exceptions, you need to define
how the field is supposed to be built for those 26 exceptions.
Click to expand...

Ok, so i finally had a chance to use the formula as i only run this
once a month. It looks like for the most part it worked but it did not
edit the r's and the t's. Is there something i should be looking at in
the formula to correct this. It is so long that it is very confusing.

Thanks,
 
Back
Top