Excel 2007 : How to copy specific information from a cell with a lot of text to a separate cell.

Hi all,

my problem is this:

1'st I'm an Excel noob, haven't really used any advanced features.

2'nd I need to copy specific information from a cell that has a lot of other text that's not needed in it.

3'rd This has to be done several thousand times.

f.ex. I've got a thousands of cells with: "Product name, compatability, product number, product dimensions, product specifications". How do I copy "compatability" on all these to a separate cell for each of the "compatability" automatically? I guess I'll have to use a macro, but does anybody know how I should code this?

Would greatly appreciate any help.

Best regards
Thomas.

I guess I'll have to use a macro

Not necessarily. Please post a sample with some typical examples and indicate what you need to extract and what "rules", if any, apply.


Regards, TMS

and what "rules", if any, apply

So, not quite. How would someone, other than yourself, determine where the data you want to extract starts and ends? It doesn't really matter if the data is extracted with a formula or VBA ... it is still essential to know those details, that is, the "rules". I'm not familiar with the Norwegian language so the words don't tell me anything.

Regards, TMS

I'm not shooting you for your ignorance of Excel. I'm just explaining that I, and others, need to go within the string to extract the data. You know why you're looking for 4x108. Why? Is there always a double quote and a space before it? Is there always a space hyphen space after it?

If it is, this works (at least, for this specific example):

=MID(A2,FIND(""" ",A2)+2,FIND(" - ",A2,FIND(""" ",A2))-FIND(""" ",A2)-1)

If you were to tell me to come visit you, but didn't tell me your address ... country, city, road, house number ... I wouldn't know how to get there. I need some guidance.


Regards, TMS

If your "compatibility string" was consistent in some manner, this would be easier. Perhaps you have a set list of all the possible compatibility strings, and its only 5-10 options? If so, you can list those in a column for reference to help. So over in column Z put your list:

4x108
5x109
4x200
2x75


Then for the string in A2, put this formula in B2:

=LOOKUP(2, 1/(--(ISNUMBER(SEARCH($Z$1:$Z$4, A2)))), $Z$1:$Z$4)

Hi JBeaucaire,

Your suggestion looks reasonable to me.
I've tried doing as you said, but I get an error when trying to save the formula in the cell.
The error is "error in the formula" or something similar to that.
I have a Norwegian version of Excel so I had to translate it. :P
I've also tried translating the formula to Norwegian, but there's still the same error.

Thanks for your help, I greatly appreciate it.

Thomas.

Why screenshots? Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

Based on the picture, the formula to get the string from C2 would be:

=LOOKUP(2, 1/(--(ISNUMBER(SEARCH($H$2:$H$11, C2)))), $H$2:$H$11)

No, there's nothing country specific about that formula other than the commas vs semicolons.

I added a named range called CODES to this worksheet. (Ctrl-F3 to see it) This named range will expand itself as you add/delete items from the H column. Ths changed the formula to:

=LOOKUP(2, 1/(--(ISNUMBER(SEARCH(Codes, C2)))), Codes)


....which I put into E2, works fine.

You can use IFERROR() to run a second formula if the first results in an error:

=IFERROR(LOOKUP(2, 1/(--(ISNUMBER(SEARCH(Codes, $C4)))), Codes), LOOKUP(2, 1/(--(ISNUMBER(SEARCH(Codes, $D4)))), Codes))


If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Put the codes in the order you want them to be checked in that table. You would want the shorter codes above the "longer" versions and it will always return the longest matching code it finds. Just assemble your column H codes, then sort the table ascending, that should do it.

Originally Posted by vikingim

Is this possible?

Everything is possible. This technique is formulaic and will give you one code from the list.

This new little addon takes your project up to a new level of complexity. Formulas cannot "insert rows", obviously, they can only display a value. So, this would require a complete specialized VBA approach.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.