Solved Excel for Mac 2011: Extracting info from one cell to another

Microsoft Excel for mac 2011 - macintosh
August 9, 2013 at 07:26:55
Specs: Macintosh
Hi, I have information on several lines in one cell that needs to be separated into several cells in Excel for Mac 2011.

Cell B2 is as follows:

Years in Practice: 20 Years
License No. and State:
MH34202 Florida
Supervisor: Jane Doe
Supervisors License No. MH38799

Cell B3 is as follows:

Years in Practice: 4 Years
School: University of Florida
Year Graduated: 2008
License No. and State:
MH10876

I need this information extracted into separate cells. The information in the core cell is separated by line breaks as shown above. The headings will match the question, i.e. Column C will be labeled Years in Practice so I need C2 to extract the number of years found in cell B2 and left blank if the information is not there. Column D will have a heading with Year Graduated and so on. Does this make sense? Not every cell containing the the core information has all the same information, as shown above.


See More: Excel for Mac 2011: Extracting info from one cell to another

Report •


#1
August 9, 2013 at 08:26:08
Please do not post your question multiple times. It messes up the archives and can get confusing if members respond in different threads.

I have deleted your earlier post.

Thanks!

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
August 9, 2013 at 09:19:42
Sorry, I thought I was posting it in different sections. I will not do that again in the future.

Report •

#3
August 9, 2013 at 10:27:32
✔ Best Answer
Since there is a lack of consistency in the way the individual data strings are laid out, it's hard to come up with a single formula that can be entered once and dragged around to cover all situations.

For example:

Year Graduated: 2008

If we assume that the 4 digit year will appear 2 characters after Year Graduated, then you can do this:

Enter Year Graduated in C1 and enter this in C2:

=IF(ISERROR(FIND(C$1,$B2)),"",MID($B2,FIND(C$1,$B2)+LEN(C$1)+2,4))

If you drag this down, it should return an empty cell in C2 (since there is no Year Graduated entry) and 2008 in C3.

License No. and State:
MH10876

In this case, the license no. (and state) are not a fixed length. Finding the beginning of the string should be no problem becasue we know it will start after License No. and State:, but determining the length is a bit more difficult.

MH34202 Florida
MH10876

If the string was always followed by Supervisor as it is in B2, we could use that as the stop, but since Supervisor may not appear in every cell, we can't use that as the stopping point.

This will take some playing with. Let me see what I can do.

BTW, I hope you are satisfied with keeping the License No. and State together as one answer. Splitting those up adds another layer of complexity to the situation.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 9, 2013 at 10:30:03
re: Sorry, I thought I was posting it in different sections.

It is also frowned upon to post the same question in 2 different sections of this forum. If you don't get an answer in one forum after a period of time, you might consider reposting in a different section, but referencing the original thread so that members can at least look back and see what has already been suggested.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
August 9, 2013 at 15:05:38
Thank you so much. Yes, it is my intention to keep the license no. and state together, they just come on two separate lines. This is the way the information came to us, but it is not much help in this format and changing each one by hand would take forever considering the amount of data we have. Thank you so much for all your help with this matter, it is deeply appreciated. Again, I will not post multiple times again.

This is going to sound stupid, but if my column letters are actually Y2 for the core info and AB1 for the Year Graduated (Heading), would the formula then be

=IF(ISERROR(FIND(AB$1,$Y2)),"",MID($Y2,FIND(AB$1,$Y2)+LEN(AB$1)+2,4))

message edited by Charity2448


Report •

#6
August 9, 2013 at 15:23:01
Never mind about the last question. I tried it and it worked perfectly :) Thank you soooo much!

Is there anyway to use CHAR(13) as the end of the string. I know that pacific formula doesn't work, but I thought since they are each on a different line, the return character could act as the end of the string. I don't really know what I am talking about, but I was wondering if that was possible. Would it be easier to transfer the entire line over instead of just certain words in the line of the cell? I wouldn't mind if the destination cell contained the entire line, for example:

School Attended: Florida University

Again, thank you for all your help. I would be lost without you!

message edited by Charity2448


Report •

#7
August 9, 2013 at 16:07:11
I used CHAR(10) for License No. and State and it worked after some finagling, but for some resaon I get can't the same formula to work for Supervisor. The issue with License No. and State is that B3 doesn't have a carriage return after the last line, so I had to deal with that as a separate issue. In any case, this formula worked for License No. and State , at least for the 2 examples posted.

=IF(ISERROR(FIND(D$1,$B2)),"",MID($B2,FIND(D$1,$B2)+LEN(D$1)+1,IF(ISERROR(FIND(CHAR(10),$B2,FIND(D$1,$B2)+LEN(D$1)+2)),256,FIND(CHAR(10),$B2,FIND(D$1,$B2)+LEN(D$1)+2)-FIND(CHAR(10),$B2,FIND(D$1,$B2)))))

You know, I'm wondering if it might be easier if I had an actual copy of the spreadsheet, although I completely understand the security and privacy issues related to that. The reason I think it might be easier is because I am copying lines from this forum and don't really know if the same character codes are being copied e.g. CHAR(10) vs CHAR(13).

I can send you an email address via private message if you are willing to send an excerpt of the spreadsheet containing enough rows to show the various ways the data is presented. Perhaps you could use Find/Replace to get rid of any personal data as long as you don't delete the end of line/new line characters.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
August 9, 2013 at 17:13:39
I would LOVE to send you the spreadsheet, that way you can see what I am dealing with. Column Y is the only column I need separated out into separate cells. The other information I was able to separate them with ;; in order to work with our upload because they all go into one category, but Column Y has separate categories for each set of information. You are my knight in shining armor for real! With your help, I am able to keep my hair in tact :) lololol I played a little bit with the School info, but I see what you mean, I would have to change the number in each cell, which is just as bad as copying and pasting the info. I wouldn't mind if the whole line including the question was in the designated cell, that would be better than cutting and pasting.

Report •

#9
August 9, 2013 at 18:23:05
I've sent you an email address via PM.

Please do not share this email address with anyone. It is temporary and will be deleted when we are done with it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
August 9, 2013 at 20:22:23
Done and done :)

Report •


Ask Question