Assume that I have "ABC" typed into my A3 cell. I want excel to exchange "A" into "X" and I need it to recognize that there is an A as the first character in that cell. Here is a formula that simply does not work but I feel like I'm close.
=IF((A3=A3,1,1,"A"),REPLACE(A3,1,1,"Q"),"")
I know that the error lies in the logical test area, I just don't know how to make it work.
You mention A & X but then use Q in the formula ?
If you're saying A should be replaced by X but only where A is first char in string (and only that instance) then:
above is case sensitive - encase first A3 in UPPER if insensitive.
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks, that definitely helps a lot. I have one more question though. What if in that cell I have
"AAA" and I want to replace the second "A" for "X"?
I was tired last night and I did not see the obvious in the formula. I can make it change whatever I want now. Thanks! I hate to do this but I got one more follow up question. What if I want it to change the first A to X and then C to Y? Basically, putting two of those formulas together, how would I go about that?
Maybe something like this:
=IF(LEFT(A1,1)="A","X",IF(LEFT(A1,1)="C","Y",LEFT(A1,1)))&MID(A1,2,100)
That is a nice suggestion, I appreciate it. However, I am trying to make something more universal. basically I am trying to get an infrastructure for a formula that would check if first character is a then insert x and if the first character is b then insert y. I think I just need to combine the above mentioned formula, it seemed simple :D
Maybe this:
Its not clear to me what you are doing, but have you looked at the instance_number argument of SUBSTITUTE?
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
I think I found my solution in one of the formulas that was attached in a previous post. If I run into anymore problems then I'll definitely ask for help. This has been extremely helpful.
I did run into one more problem, I believe that this will be it though. Here is the Formula that I am having problems with:
MID(A1,2,100)
This will continue the string of characters.
=IF(LEFT(A9,1)="Q","N/A",IF(LEFT(A9,1)="W","D",IF(LEFT(A9,1)="E","F",IF(LEFT(A9,1)="R","G",IF(LEFT(A9,1)="Y","J",IF(LEFT(A9,1)="I","L",IF(LEFT(A9,1)="O","P",IF(LEFT(A9,1)="P","N\A",IF(LEFT(A9,1)="A","X",IF(LEFT(A9,1)="S","C",IF(LEFT(A9,1)="D","F",IF(LEFT(A9,1)="F","T",IF(LEFT(A9,1)="G","Y",IF(LEFT(A9,1)="H","U",IF(LEFT(A9,1)="J","I",IF(LEFT(A9,1)="K","O",IF(LEFT(A9,1)="L","P",IF(LEFT(A9,1)="Z","N\A",IF(LEFT(A9,1)="X","D",IF(LEFT(A9,1)="C","F",IF(LEFT(A9,1)="V","G",IF(LEFT(A9,1)="B","H",IF(LEFT(A9,1)="N","J",IF(LEFT(A9,1)="M","K",IF(LEFT(A9,1)="T","H",IF(LEFT(A9,1)="U","K",LEFT(A9,1)))))))))))))))))))))))))))&MID(A9,2,100)
So right now I have it so that in case the first character happens to be Q or P or Z then it will return N/A and all the other characters. In case it is Q or P or Z then I just want excel to leave the cell blank. How would I go about that?
Why don't you just add letters in example I show you?
Then you don't need to have lot of IF's, you just need to define them in a table.
=IF(ISERROR(SEARCH(LEFT(A9,1),"PQZ")),mid("xhffftyuliopkjp gchkgddj",code(left(a9,1))-64,1)&mid(a9,2,100),"")
Last edited by snb; 12-13-2010 at 05:32 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks