How to merge a substitute and RIGHT formula together, referencing the same cell.

[JulianS96]

Hi forum,

I have been trying this for a while now but can't work out how to merge these two formulas together:
TRIM(SUBSTITUTE(B2,E2,""))
&
IFERROR((TRIM(RIGHT(C2,LEN(C2)-FIND(":",C2)))),C2)

So that it can all be contained in one formula, without the need of the helper column as I have already.

Sheet included with explanation of what I want to achieve.

[AliGW]

Not pretty, but does exactly what you've asked for:

=IFERROR((TRIM(RIGHT(TRIM(SUBSTITUTE(B2,LEFT(B2,IF(ISNUMBER(FIND(":",B2)),FIND(":",B2)-1,FIND(" ",B2,1)-1)),"")),LEN(TRIM(SUBSTITUTE(B2,LEFT(B2,IF(ISNUMBER(FIND(":",B2)),FIND(":",B2)-1,FIND(" ",B2,1)-1)),"")))-FIND(":",TRIM(SUBSTITUTE(B2,LEFT(B2,IF(ISNUMBER(FIND(":",B2)),FIND(":",B2)-1,FIND(" ",B2,1)-1)),"")))))),TRIM(SUBSTITUTE(B2,LEFT(B2,IF(ISNUMBER(FIND(":",B2)),FIND(":",B2)-1,FIND(" ",B2,1)-1)),"")))

I am sure, if you explain in words what the process is here, that there'll be a more compact alternative.

Do you still not have MS365? If you did, then the LET function would come in really handy here.

[JulianS96]

Wow that is some monster formula! I don't care, if it works it works!!!

[AliGW]

I would care! And I would care because it's making the same calculations several times over. As I said, there may well be a more refined and less consumptive alternative.

Do you have MS365??

[JulianS96]

Do you still not have MS365? If you did, then the LET function would come in really handy here.

I might do actually. on a company laptop and there's an "Autosave" function at the top left, I think that's Office 365?
Oh lol, yeah I do. just checked Account and it says "Office 365 Enterprise"

[AliGW]

Right - in that case, give me a few minutes.

[JulianS96]

I am sure, if you explain in words what the process is here, that there'll be a more compact alternative.

Ok I'll try. From Cell B2, I want to take out the text that is in Cell E2 from B2 and then remove the ":" in front of the new result.
The formulas i used may not have been the best to use, but it's what I found online.

[Phuocam]

Another way:

=TRIM(REPLACE(B2,1,IFERROR(FIND(":",B2),FIND(" ",B2,1)),""))

[AliGW]

Just as an academic exercise, here's how to use the LET function (MS365) to combine your three formulae:

=LET(c,LET(b,B2,e,IFERROR(LEFT(B2,IF(ISNUMBER(FIND(":",B2)),FIND(":",B2)-1,FIND(" ",B2,1)-1)),""),TRIM(SUBSTITUTE(b,e,""))),IFERROR((TRIM(RIGHT(c,LEN(c)-FIND(":",c)))),c))

However, as I suspected, there's a much simpler way, it seems - see post #8.

[JulianS96]

@Phuocam

That's a nice solution Pheocam! Thanks!
Looking forward to seeing Ali's solution with this "new" "LET" function as well!

[AliGW]

It's already there!

[JulianS96]

Wow thanks Ali, that's kinda what I was exactly asking for as well. A way to "hold" or "keep" the "new" result form the first formula and take it through to the end to remove the ":".
Both formulas work perfect thanks, and Thanks Ali for enlightening me to the LET function! Reminds me of the good old GCSE/ A-Level Maths days!

[AliGW]

I've just started playing with it myself, so this was fun to do.

[Glenn Kennedy]

FWiW... one formula to do the lot.

=TRANSPOSE(FILTERXML("<A><B>"&SUBSTITUTE(IF(ISNUMBER(SEARCH(":",B2)),B2,SUBSTITUTE(B2," ",":",1)),":","</B><B>",1)&"</B></A>","//B"))

[AliGW]

FILTERXML is the other new function I want to explore in my retirement. Got a bit of catching up to do there.