Combining LEFT\IF and Concatenation

[glwday]

One of my responsibilities is the deployment of software around our network. I often get lists of users and their computers names but usually not in the format I need.

For example I get

User PC
S01234 1712265
K11567 1287997
B09768 2524387

What I need is for each PC name to have it's site prefix added

User PC Prefixed Result
S01234 1712265 ST1712265
K11567 1287997 LO1287997
B09768 2524387 BS2524387

Currently I have to sort on user name and =CONCATENATE("ST",B2) copy that down the users beginning S =CONCATENATE("LO",B3) for users beginning K and so on.

Is there a way of using LEFT so that it goes something like this
IF LEFT(A2)="S" Concatenate ("ST",B2) ELSE IF LEFT(A2)="K" Concatenate ("LO",B2) ELSE IF LEFT(A2)="B" Concatenate ("BS",B2)

Thanks in adavance

[Ken Johnson]

Hi glyday,
=IF(LEFT(A2,1)="S","ST" & B2,IF(LEFT(A2,1)="K","LO" &
B2,IF(LEFT(A2,1)="B","BS" & B2,"")))
is one version
Ken Johnson

[Ron Rosenfeld]

On Fri, 10 Mar 2006 04:19:55 -0600, glwday
<glwday.24g8fa_1141986000.9627@excelforum-nospam.com> wrote:

>
>One of my responsibilities is the deployment of software around our
>network. I often get lists of users and their computers names but
>usually not in the format I need.
>
>For example I get
>
>User PC
>S01234 1712265
>K11567 1287997
>B09768 2524387
>
>What I need is for each PC name to have it's site prefix added
>
>User PC Prefixed Result
>S01234 1712265 ST1712265
>K11567 1287997 LO1287997
>B09768 2524387 BS2524387
>
>Currently I have to sort on user name and =CONCATENATE("ST",B2) copy
>that down the users beginning S =CONCATENATE("LO",B3) for users
>beginning and so on.
>
>Is there a way of using LEFT so that it goes something like this
>IF LEFT(A2)="S" Concatenate ("ST",B2) ELSE IF LEFT(A2)="K" Concatenate
>("LO",B2) ELSE IF LEFT(A2)="B" Concatenate ("BS",B2)
>
>Thanks in adavance

Although the IF statement can be used with just three users, it becomes
increasingly cumbersome for more users, and impossible if you have more than
eight users.

Accordingly I would suggest a lookup table which would be much more easy to
extend.

For the examples you give, the lookup table (tbl) would look like:

S ST
K LO
B BS

and the formula would look like:

=VLOOKUP(LEFT(User,1),tbl,2,FALSE)&PC

Where User and PC are named ranges referring to your data ranges; tbl is the
named range referring to the Table.


--ron

[glwday]

Thanks guys for your help.

While I was waiting I finally got my version to work with

=IF(LEFT(A2)="S",CONCATENATE("ST",B2),IF(LEFT(A2)="K",CONCATENATE("LO",B2),IF(LEFT(A2)="B",CONCATENATE("BS",B2))))

Keep up the good work

GLW