Forum Discussion

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

excel

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
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
  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.
  - WhiteBeltInExcel
    Copper Contributor
    Mar 01, 2023
    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.