How to Replace Nos {1,2,3,4,5,6,7,8,9} with some text appended to the number and a comma?
Dear Forum,
If I have a value in a cell such as 12345 then I need that to be shown as below:
Sheet1,Sheet2,Sheet3,Sheet4,Sheet5
If the value is 12040 then I need to just show:
Sheet1,Sheet2,Sheet4
I dont how to use the Replace, Substitute Function for a single character?
Warm Regards
e4excel
Last edited by e4excel; 03-15-2012 at 02:43 PM.
A bunch of nested SUBSTITUTE Functions should work:
The cell reference appears only once in inner most nested function
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks NBVC, but ,Is there no way where we can compress the code like no way of iteration..
Actually I was trying to even replace the "0" zeroes with nothing but even that happens only for the first zero and the rest remain as it is..
This formula replaces all 0's... as long as you don't enter anything for the 4th option argument (instance_num), then all 0's should get replaced (make sure they are not the letter O instead).
Substitute is not that versatile..
If you are willing to use VBA, the use this udf:
and then formula:Please Login or Register to view this content.
=SUBSTITUTE(TRIM(SUBSTITUTE(aconcat("Sheet"&MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)," "),"Sheet0"," "))," ",",")
confirmed with CTRL+SHIFT+ENTER not just ENTER
If you have teh Morefunc.xll addin, then you can use:
=SUBSTITUTE(TRIM(SUBSTITUTE(MCONCAT("Sheet"&MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)," "),"Sheet0"," "))," ",",")
confirmed with CTRL+SHIFT+ENTER not just ENTER
Last edited by NBVC; 03-15-2012 at 12:03 PM.
Wow NBVC, Thanks can you please explain what the ROW(INDIRECT("1:"&LEN(A1))) in the line MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)," ") does?
It works the way I wanted, thnks once again..
It will take some time for me to absorb the UDF first
e4xcel
As you may have noted the UDF is not mine:
so I won't take credit... but it is a popular piece of code used for this purpose.Please Login or Register to view this content.
This part ROW(INDIRECT("1:"&LEN(A1))) when part of an array formula, simply creates an array from 1 to the length of the string in A1, so that exactly the number of characters found in A1 are checked in the MID function one at a time.
NBVC,
Actually, I was trying to do the same thing but without any results..
I was wanting to Replace every no from 1 to 9 with the word "Sheet" & the same no which is replaced and also with an additional comma...
where F2 can contain any of these numbers such as below:PHP Code:REPLACE(RIGHT(F2,ROW(INDIRECT("1:5"))),1,1,"Sheet"&RIGHT(F2,ROW(INDIRECT("1:5")))&",")
12345
12345
12345
12345
12345
12040
10340
10040
10000
5
whererver there is a zero it should be ignored and it should consider only the actual numbers from 1 to 9..
Your function definitely works but I have not really come to terms with the usage of the ROW(INDIRECT("1:"&...) portion..
Can you please elaborate a little more, I was trying to help someone else with the SheetNames , basically its a concept of Single Value Lookup present in Multiple Sheets..
http://www.excelforum.com/excel-work...e-in-cell.html
I will rather attach the sheet for reference..
Not sure what your question is?
If you use the Evaluate formula tool found in the Formulas tab, you will see how it breaks out.
If you have 13579, then ROW(INDIRECT("1:"&LEN(A1))) breaks down to: ROW(INDIRECT("1:"&5)) where 5 is the length of the text 13579. That further breaks to ROW($1:$5) and when applied to MID(A1,ROW($A$1:$A5),1), it expands out to MID(A1,{1,2,3,4,5},1) so now each of the five characters in A1 gets extracted by the MID function so you get {"1","3","5","7","9"} and each gets concatenated to the word "Sheet", even if there is a 0 in the number... there is another SUBSTITUTE added to clear out any "Sheet0" that may be included, leaving only the desired text string.
No NBVC, I was asking more about the ROW(Indirect()) usage and nothing specifically to do with your code it works but before putting the query even I was trying and knew that the row(indirect()) had a role to play but could not actually employ it correctly..
I thought if I get the Sheet Nos wherever the Lookup Value Matched then I needed a way of just breaking the number series someway and prefixing it with "Sheet" or "Plan" and the tail with a comma..
So if the answer was 12040 then I thought if there was a possibilty of just replacing the numbers between 1 till 9 excluding zero with the word "Sheet" but unfortunately I couldnt do it..
Warm REgards
e4excel
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks