Dear all, concatenate cell value with substitute
i want like below in the cell A1 & A2 other then cell keeping as blank
CELL B1 CELL C1 CELL D1 BSE01 ETM2-0 0 BSE01 ETM2-0 1
It is something like asBSE01_0ETM2_MS-0 BSE01_0ETM2_MS-1
A1= B1&"_"&SUBSTITUTE(C1,"ETM2","")&"ETM2"&"_MS-"&D1
Last edited by nur2544; 08-30-2014 at 12:54 PM.
Try this...
Formula:
=B1&"_"&LEFT(C1,(FIND("-",C1)-1))&"_MS-"&D1
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I have just noticed that there is an 0 in front of ETM2 in your solution. Is this always a zero, or is it the last digit in B1, which may be variable?
Hi Glenn Kennedy, thanks for replay , it is not working , its output is giving as
but it should beBSE01_ETM2_MS-0 BSE01_ETM2_MS-1
i am having as belowBSE01_0ETM2_MS-0 BSE01_0ETM2_MS-1
CELL B1 CELL C1 CELL D1 BSE01 ETM2-2 0 BSE01 ETM2-2 1 BSE01 ETM2-3 0 BSE01 ETM2-3 1 BSE01 ETM2-4 0 BSE01 ETM2-4 1 BSE01 ETM2-5 0 BSE01 ETM2-5 1 BSE01 ETM2-6 0 BSE01 ETM2-6 1
Last edited by nur2544; 08-30-2014 at 12:50 PM.
Same formula as post #5, but in a sheet.
OK got it. I'll be back in 5 minutes...
your everything is ok, jut need to change work for below field
if ETM2-1 then it will be 1ETM2 if ETM2-25 then it will be 25ETM2 if ETM2-10 then it will be 10ETM2 if ETM2-9 then it will be 9ETM2
OK. Try this out...
ETM2 this common for all text. just remove dash(-) & after the numeric value keep in front of ETM2
yes now i got my desire output , thanks for that . one issue if cell having blank it returns to #VALUE! . i don't want to see this . it should be blank as the field having no data.
I was aware of this. See post # 3. To repeat. Is the "missing" zero, always a zero?
If it is always a zero, this should be OK.
Formula:=B1&"_0"&LEFT(C1,(FIND("-",C1)-1))&"_MS-"&D1
no it will vary , See post # 4
i want as below
BSE01_2ETM2_MS-0 BSE01_2ETM2_MS-1 BSE01_3ETM2_MS-0 BSE01_3ETM2_MS-1 BSE01_4ETM2_MS-0 BSE01_4ETM2_MS-1 BSE01_5ETM2_MS-0 BSE01_5ETM2_MS-1 BSE01_6ETM2_MS-0 BSE01_6ETM2_MS-1
So.... if it is varying, where is it in your source text string?
In this case: BSE01 ETM2-2 0
What digit do you want to appear in front of "ETM" ? Is it the second 2 in ETM2-2?
Done. See attached. Modified formula (pink) now runs below your data and is blank.
If that's it, can you mark the thread as solved and (preferably) say thanks by clicking the add Reputation button at the bottom of this post?
Hi Glenn Kennedy
Yes it works perfectly & my requirements is 100% ok. You did excellent job for me , I really appreciate it . it will help me a lots , thanks for your valuable time…
Hi Glenn Kennedy,
Yes it works perfectly & my requirements is 100% ok. You did excellent job for me , I really appreciate it . it will help me a lots , thanks for your valuable time…
Last edited by nur2544; 08-30-2014 at 01:27 PM.
You're welcome - which part of Bangladesh are you from? I've been in Dhaka four or five times and in Khulna once.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks