Excel help

[lsg1r]

I have a cell that has a combination of text within asterisks, text/email within $ signs and then an entry from a range of entries (nd, am, pm, ndam, ndpm, sat, ndsat, bw, ndbw). It's possible that none of these are in the cell or all of them.

I can get the cell the cell to read the range of entries and put the relevent entry into the cell and leave empty if it's not there using IF formula, the issue is that I want to ignore the content between the asterisks and the content between the cells and using IF doesn't seem to do this.

Is there a way that I can do this?

Examples below with result I'd like:
1 - *text here nd* $ndemail@email$ nd = nd
2 - $email$ *text* SAT = sat
3 - *text* = blank
4 - ndbw = ndbw

Thanks for any help

 

[lsg1r]

Can't you just handle each separate thing in a separate column, and then concatenate?

Sadly no, it's coming in from another file in one cell.... I'm basically trying to separate multiple entries in one cell into different cells.

 

[lsg1r]

Have you tried asking ChatGPT?

yeah... it's giving answers that don't work

 

[lsg1r]

Does it have to be a formula? Could just go nuts with cell to columns (or w/e it is called).

It doesn't have to be a formula but I can't say I've done the cell to columns before, well not knowingly lol. The cells aren't next to each other if that makes a difference

 

[lsg1r]

If you need to seperate these out into seperate cells based such a large variety of delimiters you will need VBA or if it's an online 365 excel you can use Python

Even when it's really only 3 sections?
The ** bit, The $$ bit both of which I basically want to ignore and then the answer based on the remaining bit

I can't say I'm good with python or vba if I'm being honest.

 

[lsg1r]

I don't have excel to hand right now but I think I would try doing something like:
- Find the first asterisk
- Find the last asterisk
- concatenate string from before and after each location.
- same thing with dollars.

Or Just delete all text before the location of the last asterisk / dollar in the string if they always come before the text you want.

Would need to make sure none of the text strings you want contain asterisks or $s though because that could mess things up and need more handling.

Sorry if it doesn't work lol, that's what I'd try first though.

Honestly that was my kind of my thinking... find the ** and turn it into a blank, find the $$ and turn it into a blank so they're both ignored, neither should include a $ or *.

I did try using concatenate when trying bing but it never seemed to work. I'll give it another go tomorrow.