2

I'm using Excel 2003. I have a spreadsheet that currently has 500 rows of data and just one column (A). For example, the first five rows look like this:

          A
  1  loremipsum=fufu.com
  2  heyheyhey=wuwu.com
  3  youyouyouyou=dudu.com 
  4  helloworld=huhu.com
  5  happyhappy=mumu.com

I want to reformat all 500 rows so that the portion of the data after the = sign on each row is moved to a new column (B), and the = sign is deleted. Thus, the data will occupy in two columns (A & B), and the first five rows (1-5) will look like this:

         A            B 
 1  loremipsum     fufu.com
 2  heyheyhey      wuwu.com 
 3  youyouyouyou   dudu.com 
 4  helloworld     huhu.com  
 5  happyhappy     mumu.com

Can you tell me how to set up a macro? (assuming the solution entails a trip on the macro magic carpet)

2 Answers 2

0

Use this formulas

column B: =MID(A1,1,FIND("=",A1)-1) → autofill down
column C: =MID(A1,FIND("=",A1)+1,LEN(A1)) → autofill down
delete column A afterwards

1
  • Thank you for cleaning up (editing) the formatting of my question. Also, thank you for your answer. As it turns out, I unintentionally neglected to include the fact that the data in some of the rows contained more than one "=" sign, e.g.: loremipsum=fufu.com/foo=foo?moo=moo . However, your solution still works just the way you suggested. It creates a break after the first "=" sign, and dumps the rest of the line (including any additional "=" signs) into the next column. Perfect! Thank you again.
    – user18467
    Jul 11, 2013 at 0:04
1

Select the column, click Data > Text to columns > Delimited > check "Other" and enter a = sign > finish

2
  • thank you for your answer. As presented, the sample data set and the outcome I defined for it would be handled well by your solution. Regrettably, I neglected to include the fact that the data in some of the rows contains more than one "=" sign, e.g.: loremipsum=fufu.com/foo=foo?moo=moo . So your solution solved the issue as I presented it, but not as I should have presented it. Perhaps you or someone in the community can instruct me how best to acknowledge your contribution under these circumstances? Regardless....thank you!
    – user18467
    Jul 11, 2013 at 0:01
  • To acknowledge an answer as useful you can upvote it by clicking the arrow above the number. Once you have 15 rep points, you can upvote answers.
    – teylyn
    Jul 11, 2013 at 0:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .