Forum Discussion

WhiteBeltInExcel's avatar
WhiteBeltInExcel
Copper Contributor
Feb 28, 2023

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

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?

excel

5 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 28, 2023
    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") & "; ")))
    • WhiteBeltInExcel's avatar
      WhiteBeltInExcel
      Copper Contributor
      Feb 28, 2023

      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. 😄

       

      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. 😞
       

      • mtarler's avatar
        mtarler
        Silver Contributor
        Feb 28, 2023
        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.

Resources