Function or Formula to replace a dynamic string with a NULL value

You might be able to do it with some complicated combination of LEFT/MID/SUBSTITUTE/FIND/SEARCH, possibly with the help of some helper columns, but it will depend entirely on the make-up of the [useful data string] and the [junk].

For example, it might be possible to do it with this mix of useful letters and junk numbers:

thisisuseful123,soisthis456,andthis7890,

making it into:

thisisuseful;soisthis;andthis

But this, with both useful data and junk being a mix of letters and numbers, would be impossible:

thisnumber12isuseful99junk99,andthisnumber34too98junk98,andthis56aswell97junk97,

The reason is that there would be no way of knowing that the correct scrubbed version should be:

thisnumber12isuseful;andthisnumber34too;andthis56aswell

instead of:

thisnumber;andthisnumber;andthis

or:

thisnumber12isuseful99junk;andthisnumber34too98junk;andthis56aswell97junk

Can you upload a sample file to let us see what sort of combinations you have? We can then let you know if it's possible but difficult, or impossible.
(Click 'Go Advanced' below the reply box, then 'Manage Attachments' - make sure you don't include any confidential data.)

I agree that having a more rigorous definition of what your data looks like is essential.

I am also inclined to say this should be solved with VBA. regex pattern matching is ideal for this sort of thing.

Not sure I understand the requirements. The upload doesn't seem to match the description.

Assuming 'junk' is in the pattern "###:" I tried this anyway.

There is a 'new' feature starting in Excel 2013 called Flash fill.

These are the steps I took:

1. Apply filter to the data.
2. Filter out Blanks in column B.
3. Choose the most complex string (most "####:").
4. I chose "101:Sr. Project Coordinator, James Schaefer ; 100:Sr. Project Coordinator, Sharon Peetro" from row 7.
5. I copy/pasted that single item in D7.
6. I deleted all the 'junk'.
7. With that cell active click Data > Flash fill.
8. Then remove Filters.

This worked at my end with the example posted.

to me it looks like the values you want are after , and if there is a ; we want to look for a second, third etc , character.

are they all formatted like this?

Anyway, try something like this if so (in C2):

Please Login or Register  to view this content.

you can add onto the end of it in the same way for further names if needed, idk how many iterations you would need... Got slightly confused towards the end of it myself, a little tired...

anyway you just need to reference where the stuff is to get it to work.

For instance,

Please Login or Register  to view this content.

finds the second occurrence of "," which solves the issue. You'd have to nest that again for a third occurrence, etc. So, as you can see, this formula format will very quickly get out of control, but it will work...

Going to bed now, gl

You're welcome. Thanks for the feedback.

Afraid I can't help you with a VBA solution. It's my short suit, but standby. I'm sure someone is watching.

sryme,

There is a User Defined Function written by tigeravatar that I often find useful. You can find it here:
http://www.excelforum.com/tips-and-t...ml#post3096647

Try this array-entered formula in D4 (filtered out blanks, again) and filled down

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

The syntax of that function is =concatall(array or range, separator). I used "" for the separator.