I could use help trying to replace data in the same cell.

[WhiteBeltInExcel]

Hi,

I looked online and tried substitute but that didn't give me anything I could copy/paste in a field. I tried replace but the macro wouldn't work because I couldn't setup the index.

I have multiple names in the following format in one cell.
Lastname, Firstname jobtitle

I would like to take those and convert them to the below and level them in the cell with a ";" semicolon to divide them.

NTID@RANDOM

I have another excel with all the conversions that I have looked up and matched all the people.

An example would be this:
Sheet 1:

A1: lastname, firstname, jobtitle lastname firstname jobtitle lastname, firstname, jobtitle

Sheet 2:

A1: lastname, firstname, jobtitle

B1: NTID@RANDOM 

What I Need is:
Sheet 1:

A1: NTID@RANDOM;NTID@RANDOM;NTID@RANDOM;NTID@RANDOM 



Is this possible? Or am I stuck using the replace function on each one, one at time?

[#2]

a) you can't technically replace data in a cell using spreadsheet functions. That said you could have a function like =LET(in, "paste the string you want to work on here", ... ) and then you sort of have that string in that cell and replacing it with the data you want.
b) a sample sheet would be really helpful to make sure the format is set up right and to test on. For example on sheet 2 I assume you mean col A has all the names and col B has the e-mail addresses?
c) the whole format is difficult because you have "," between lastname, firstname, jobtitle but only a space between entries. What if the job title has a space AND could the last name have a space?
so if I assume extra spaces are NOT allowed then off the cuff without any testing I see something like this possible:
=LET(in, "put string input here",
entryList, TEXTSPLIT(in, ", ", " "),
REDUCE("", SEQUENCE(ROWS(entryList)), LAMBDA( p, q,
p & XLOOKUP( TEXTJOIN(", ",, CHOOSEROWS( entryList, q ) ), Sheet2!A1:A100, Sheet2!B1:B100, "not_found") & "; ")))

[#3]

@mtarler

Thank you for the fast reply, I appreciate it and you! :)

It is difficult. Its a data pull that I will have 1 name I will need to remove. Then I will have to push it back up to the APP. The APP requires me to convert all the information in those fields to NTID so it can be consumed by incredible archaic application. :D

I'll go over what you said to see if will save me time in the future.

Again thank for your time and confirmation that it's not likely possible. :(
 

[#4]

again, samples and such would help a lot. But the sounds of it is that you don't necessarily need it replaced in the SAME cell. Wouldn't it be easier to paste the data into A1 and have the formula in A2 and then copy and paste the result from A2 back into your system? Of you could have the input on Sheet1 and the output on Sheet3 so then you can export or save that sheet?
More than likely it IS possible, the question is WHAT it is that is possible.

[#5]

@mtarler 

The issue is I have 670+ cells I have to do this on. I will have additional data pulls incoming. I was looking to see if I could speed it up. Sheet 2 has no real bearing on the task but it is what I use to find duplicate names through multiple requests. It saves the time of repeatedly looking up NTIDs.

[#6]

I don't even know if I understand what "this" is. You have in the example
LastName, FirstName, jobTitle
LastName, FirstName, jobTitle
LastName, FirstName, jobTitle
But I assumed that was indicative of many different individuals with different last names, first name and job titles and that on sheet2 was the same thing that there is a list of each name and replacement value. but now I don't know, is it the same name repeated?
As for the 670+ cells, fine, you have a formula that does what you need and you put those 670+ in cells A1:A670 and the formula in B1:B670 (or use dynamic arrays to apply the 1 formula to the whole set)
but like I said, it is hard to help if I don't understand what you need.