text change

  • Thread starter Thread starter John
  • Start date Start date
J

John

I'll try this again as I accidentally hit the enter key after typing the
subject. In a spreadsheet I have all of my staff listed by their abbreviated
names used to identify them on our server. I want to be able to convert
there abbreviated names like "gcarls" to "Carlson, Glen". I think one of the
If formualtions will work but I'm having difficulty. Can anyone please tell
me how to do this?
 
John,

How do we establish that gcarls becomes Carlson, Glen?

Why for example doesn't that same username become Carlton, Geoff or any
other variation?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Hi,
Let's assume you have a list of full names in sheet2 in column A and the
abbreviate name in column B, then in sheet 1 you have the abbreviate names in
column A, so in column B enter

=index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))
 
To be clearer, what really want to do is find each incidence of "gcarls" in a
column and have each instance converted/replaced with "Carlson, Glen"
 
Hi John,
How do you get Carlson, Glen from Gcarls, this is what we need you to
explain us in order to help you
 
gcarls is his logon name in our system and will never change. I get reports
that list things he does by his logon name and I want to convert it to his
full name.
 
John.

We really do want to help but your not providing sufficient information.
gcarls is his logon name in our system and will never change.
Click to expand...

What is 'our system'?
I get reports that list things he does by his logon name
Click to expand...

Where are these reports coming from? What is the reporting tool?

Does 'the system' or 'Excel' have a table somewhere that lists full names
and logon names? If it does you can query that table with a lookup in the way
Eduardo showed you.

In short, unless you have such a table that is available to Excel then what
your asking can't be done.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
I get raw data from our server - sent by a tech person - the tech person
can't provide me more than this - this raw data is sent in an excel
spreadsheet. gcarls is one of 13-14 staff memebrs who are idnetified in the
raw data by their logon names - for logon to our server and in to a specific
custom program where they enter data regarding their activities. I can
create a table of these logon names say in col A and their full names in col
B - no big problem there.
 
John,
I get raw data from our server - sent by a tech person - the tech person
can't provide me more than this -
Click to expand...

Then it seems as if the 'server' doesn't hold a table of these nsames so you
need to create your own in 2 columns in Excel. Col A = Logion name & Col B
real name and then this formula


=VLOOKUP(C2,A2:B17,2,FALSE)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
In my workbook - on sheet1 the abbrev names are in col f - sheet2 has both
full and abbrev names in cols A & B respectively. So I modified your formula
below to and entered it in col G But I get #N/A in col G where I'm trying to
list full names.

Modified formula
=index(sheet2!$A$1:$A$10000,match(f1,$B$1:$B$10000,0))

Orig formula
=index(sheet2!$A$1:$A$10000,match(a1,$B$1:$B$10000,0))

I'm obviously missing something - formulas don't come easy to me.
 
Hi John,
if the formula doesn't find the abbreviation will give a #N/A error, maybe
you have blank spaces somewhere in the abreviation, use trim option in
another column

=trim(A1)

then overwritte your column A with this information pasting it as values, do
the same in both sheets
 
The message I'm getting says "a value is not available to the formula or
function". My full names column is formatted as Lastname, Firstname but even
when I just entered the Last name (i.e. no spaces or commas) I still got the
message. I adjusted the formula to look at only the range of cells in the
columns that actually contains names or abbreviations and still the same
message. I've also tried formatting the text as number or general and
nothing. Is it ok for thetext to be formatted as text? I also made sure
there were no ' preceeding names or abbrevations.
 
Ok, do this, copy your abb name from the list that contains the full name
into the other sheet if the formula works, means that the abb are not exactly
the same
 
Back
Top