0

I have two columns, like so (in Excel 2013):

               A                  B         ..
1     <Title text="x" />        purple
2     <Title text="x" />        Bronze
3     <Title text="x" />        Violet
4     <Title text="x" />        Cyan
.     <Title text="x" />        Magenta

and the rows go on for quite a while.

My goal is to have the x's in column A replaced with the adjacent string in column B.

My approach is as follows: I have a column filled with strings (Purple, Bronze etc.), which I want to replace the x in <Title text="x" /> (instead of having to do the tedious act of copying and pasting it for every string). To do this I am trying to find a way to have every string fill in the x and appear in a tertiary column - say column C.

I'd appreciate any answer, though try to avoid using macros, since I will most likely forget the procedure in the future.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Nikita
  • 609
  • 2
  • 7
  • 18
  • Without VBA - or by doing a series of time-consuming Find & Replaces - you would not be able to perform the replacements in the same cells in which the data currently resides. A formula-based solution would be reasonably straightforward if you were happy with the results being in an additional column, however. – XOR LX Jun 24 '14 at 06:38
  • Yeah, of course. I simply want to have rows of XML with the adjacent strings in column B. Whether the result ends up in column C or D doesn't matter. – Nikita Jun 24 '14 at 06:43
  • Are the xs to be replaced always surrounded by quotation marks? If not, what do they have in common that would differentiate them from other occurrences of that letter in the string which are not to be replaced? – XOR LX Jun 24 '14 at 06:51
  • 1
    If column A always looks the same you don't really need to replace anything, something like `=""` would to the trick. If the column contains different things and you always need to replace the string `"x"` you can `SEARCH` for it and use some `LEFT`, `MID` and `RIGHT` calls to concatenate the results. – xificurC Jun 24 '14 at 07:53
  • I am not familiar with concentrating things, but I wondered if there is a way to write `=""`, but with the B2 replaced with an universal identifier for the adjacent column. So if I was to paste that in to A55 that the code would acknowledge that x needs to be replaced with what is in B55. – Nikita Jun 25 '14 at 09:22

1 Answers1

1

Since you mention "a certain character" I take it your x is a literal so would suggest: 

=SUBSTITUTE(A1,"""x""",""""&B1&"""")

in Row1 copied down. That is a similar approach to @xificurC's but would be more flexible for whatever lies on either side of "x".

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • I appreciate your suggestion, though it won't be of use to me in this scenario. I have an almost unlimited amount of possible rows. I would have to write B1 for A1 and along the road A19987 for B19987 and so on. Is it possible to have an identifier that will reference the adjacent column or something along those lines? – Nikita Jun 25 '14 at 21:10
  • It was ignorance on my part. I only had to use it once in the C column , on the first row and than copy it for all the other rows and it would change the A1 and B1 to the corresponding rows. Thank you for your solution. – Nikita Jun 25 '14 at 23:35