Is there a way of removing every character before a specific character and at the same time add a pr-CodePudding

I have the following formula extracting data from another cell:

=ARRAYFORMULA(IF(OR(ISBLANK(D10),REGEXMATCH(D10, "\*$"),REGEXMATCH(D10,"RECYCLE.BIN"),REGEXMATCH(D10,"System Volume Information")),,RIGHT(D10,LEN(D10)-(FIND("\",D10) 0))))

It's there a way of incorporate this next section of a formula together with "RIGHT"? So it will remove everything before the backslash "" and also the add " └─ " prefix?

"     └─     " & D10

I've tried so many different things like adding this part at the end:

RIGHT(D10,LEN(D10)-(FIND("\",D10) 0) ("     └─     " & D10))

I expect to have the prefix added after characters are removed.

Thanks

CodePudding user response：

The following formula should produce the behavior you desire (replacing all characters before a backslash in cell D10 with └─ ).

=REGEXREPLACE(D10,".*\\","     └─     ")

Incorporating the rest of your formula directly as written:

=ARRAYFORMULA(IF(OR(ISBLANK(D10),REGEXMATCH(D10, "\*$"),REGEXMATCH(D10,"RECYCLE.BIN"),REGEXMATCH(D10,"System Volume Information")),,REGEXREPLACE(D10,".*\\","     └─     ")))

As an alternative that improves efficiency:

=IF(REGEXMATCH(D10,"\*$|RECYCLE.BIN|System Volume Information|^$"),,REGEXREPLACE(D10,".*\\"," └─ "))

Functions Used: