Excel and a Dynamic Text Alteration Example

//Excel and a Dynamic Text Alteration Example

Excel and a Dynamic Text Alteration Example

Today was not a terrible day. I had some good experiences with Excel today. Basically we had 2,226 lines of text where the message was “itemname+itemlevel” so if it was a +1 sword it would say “sword+1”. This syntax annoyed me to such a grievous degree that I decided I needed to fix all 2.2K lines. However I’m as frugal as I am anal about things and I needed to find a way to do it quickly. The problem was as follows:

 

Itemname
Itemname+1
Itemname+2
Itemname+3
Itemname+4
Itemname+5
Itemname+6
Itemname+7
Itemname+8
Itemname+9
Itemname+10

  This was repeated ad nauseum with a few exceptions, those exceptions along with the 11 entries per item is why we ended up with 2,226 as opposed to 2220 or something like that. Anyways my solution to this problem was two fold. I wanted the number at the front (with a space) and the latter numbers removed. What I settled upon was a second column with the following.

=A1
=”+1 “&LEFT(A2, LEN(A2)-2)
=”+2 “&LEFT(A3, LEN(A3)-2)
=”+3 “&LEFT(A4, LEN(A4)-2)
=”+4 “&LEFT(A5, LEN(A5)-2)
=”+5 “&LEFT(A6, LEN(A6)-2)
=”+6 “&LEFT(A7, LEN(A7)-2)
=”+7 “&LEFT(A8, LEN(A8)-2)
=”+8 “&LEFT(A9, LEN(A9)-2)
=”+9 “&LEFT(A10, LEN(A10)-2)
=”+10 “&LEFT(A11, LEN(A11)-3)

  What this did was look at the cell just to the left of it (well specifically the cells listed) and create a new cell that read “+itemlevel itemname”. It removed 2 characters from +1-+9 because those are only 2 additional characters and removed 3 characters from +10.

  I used “len” to recover the length of the line dynamically so that no matter how big the items name was this formula would still work because it would take the total length and remove 2 from it (counting from left to right up until the final 2 or 3 characters)!

  I then copied this row and pasted it beneath itself. Then I copied that duplicate and pasted them so that I was editing 40 at a time. I then copied that 40 and pasted it again so that I’d be editing 80 at a time. I repeated this a few more times until I was done.

  Then you simply copy the new cells and paste over the old ones as “value” I believe and BLAMO! I was so incredibly pleased with it. I felt terrible physically (and consequently emotionally) all day, so having this moment of victory was something that delighted me wholly.

  So if you are someone out there looking for a solution similar to this, I hope this helps. I couldn’t find anything myself and had to rig this together on the spot.

By | 2013-06-04T20:19:08+00:00 February 19th, 2013|Journal|2 Comments