Help needed with Variation in formula!

[taurean]

Hi,

Background:
I have been visiting mrexcel forum for improving my formula writing skills. And this forum and the guys on this forum are absolutely fantastic . Apart from this, I have a small group of office-mates who are like minded in this endeavor. Any one of us creates an academic problem (for which we try out formula based solutions.

Problem:
Following is one of those cases:

Excel Workbook
	A	B	C
1	Raw Data	Expected Result	Formula
2	ab-cd-e-f-gh-cd	ab-cd-e-f-gh\cd	ab-cd-e-f-gh\cd
3	bb-cde-gh	bb-cde\gh	bb-cde\gh
4	c-d-e-ffff-g	c-d-e-ffff\g
5	e-f-g	e-f\g
6	cdef-ghjk-lef	cdef-ghjk\lef
Sheet1

The task is: replacing the last "-" character with "\"

Above formulas (especially SUBSTITUTE for its flexibility) work nicely however I'd like to get some more advice regarding following:

Request:
The REPLACE formula is a bit of a mess (for me at least as I wrote it). Here are the things I'd like to have advice on:
1. What are the other ways (Formula Based) to get above results?
2. Is there any way that we can auto generate ARRAY CONSTANTS from 1 to LEN(A2) like {1:LEN(A2} instead of hard coding them?
3. Is there anyway we can REVERSE the string as that will make FIND work?

And we all have same config. at office Excel 2007 and OS: XP

 

[Jonmo1]

1. I think your first formula in C2 is the best method to obtain desired results.
2. You can use something like ROW(A1:INDEX(A:A,LEN(A1)))
=REPLACE(A3,SUMPRODUCT(--(ISNUMBER(FIND("-",A3,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20})))),1,"\")

=REPLACE(A3,SUMPRODUCT(--(ISNUMBER(FIND("-",A3,ROW(A$1:INDEX(A:A,LEN(A3))))))),1,"\")


3. Not very easily...

 

[taurean]

Thank you very much, that is much better than hardcoding the Array Constants as it will handle any number of characters. That improved REPLACE formula flexibility.

For 3, creating UDF will be the route I suppose. Which we don't consider as we work only on formula as that is the intent.

Thanks once again

 

[taurean]

I spent some time on the logic provided and found one more combination that works:
=REPLACE(A1,LARGE(IF(MID(A1,ROW(A:A),1)="_",ROW(A:A)),1),1,"\")

This is ARRAY formula. But I read a post (by user: srizki) that Aladin Akyurek has recommended not to use a lot of them as it affects the speed adversely.

In day to day instances I do not need to use so many variations but coming here onto this forum has changed my lookout towards Excel in general. Sometimes it feels like magic, no less!