**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi I was wondering if you can extract data from a column and insert it into another

Solved by S. C. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
13/08/2018 - 12:35

Hi

Excelchat Expert
13/08/2018 - 12:35

Hello

Excelchat Expert
13/08/2018 - 12:35

Welcome to got it pro

User
13/08/2018 - 12:35

Thanks

User
13/08/2018 - 12:35

Ok can I show you what's going on in the blank doc?

Excelchat Expert
13/08/2018 - 12:35

Sure, that would be a great help.

Excelchat Expert
13/08/2018 - 12:36

Please go ahean.

Excelchat Expert
13/08/2018 - 12:36

Ahead.

User
13/08/2018 - 12:36

ok

User
13/08/2018 - 12:36

so I have a big document with the same format

User
13/08/2018 - 12:37

specification and then thickness width and lenght

User
13/08/2018 - 12:37

in the specification cell there is the values to the thickness width and lenght

User
13/08/2018 - 12:37

and my task is to fill in row b c d

Excelchat Expert
13/08/2018 - 12:37

Hmm, I'm listening..

User
13/08/2018 - 12:37

with the information, i was wondering if there was a formula or something i could use to automate this

Excelchat Expert
13/08/2018 - 12:38

Can you please point me which denotes Thickness/Width/Length in Specification?

User
13/08/2018 - 12:38

i have them in bold

User
13/08/2018 - 12:39

Do you see?

Excelchat Expert
13/08/2018 - 12:39

So they always appears in this format?

User
13/08/2018 - 12:39

yes

Excelchat Expert
13/08/2018 - 12:40

Thickness (value) mm

User
13/08/2018 - 12:40

there might be a few cases that instead of 8 lines (above thickness) there may be 9 or 10 or 7 or 7

Excelchat Expert
13/08/2018 - 12:40

Width (value) mm

User
13/08/2018 - 12:40

??

User
13/08/2018 - 12:40

yes that's what I need to know

Excelchat Expert
13/08/2018 - 12:41

What I meant is the information always appears like this? "Thickness 24+ mm
Width 100+ mm
Length 4 m"

User
13/08/2018 - 12:41

yes

Excelchat Expert
13/08/2018 - 12:41

Alright, a formula can be written then.

Excelchat Expert
13/08/2018 - 12:42

Please show 2/3 more examples in the cells below. In the meantime I'll write a formula.

User
13/08/2018 - 12:42

ok thanks

Excelchat Expert
13/08/2018 - 12:45

Please stay with me. I need some more time.

User
13/08/2018 - 12:45

ok

User
13/08/2018 - 12:45

I only have 9.5 mins left though.

Excelchat Expert
13/08/2018 - 12:46

We don't need to worry about time, we can extend to as long as 1 hour if needed.

User
13/08/2018 - 12:46

ok

User
13/08/2018 - 12:50

do you still think it is possible

Excelchat Expert
13/08/2018 - 12:50

Yeah of course, I'm almost done.

User
13/08/2018 - 12:50

awesome!

Excelchat Expert
13/08/2018 - 12:53

Please see result of B

User
13/08/2018 - 12:53

is it possible you could put mm and remove thickness from the query (answer)

User
13/08/2018 - 12:53

by mm i mean the value after the number

Excelchat Expert
13/08/2018 - 12:53

Yeah. Just a minute please.

User
13/08/2018 - 12:54

very good job though, you are really smart :D

Excelchat Expert
13/08/2018 - 12:54

Sorry, what do you mean by that? "by mm i mean the value after the number"

User
13/08/2018 - 12:54

like the value, cm, mm or m

Excelchat Expert
13/08/2018 - 12:54

There won't always be mm?

User
13/08/2018 - 12:54

i know

User
13/08/2018 - 12:55

what i mean is

User
13/08/2018 - 12:55

just to put the value with the number

User
13/08/2018 - 12:55

for example right now you have thickness 24+

User
13/08/2018 - 12:55

the goal is to have : 24+ MM

User
13/08/2018 - 12:55

the unit of measurement

Excelchat Expert
13/08/2018 - 12:55

Sorry I said it is possible on the basis that there will always be "mm" after the value.

User
13/08/2018 - 12:56

is it not possible you can put the unit of measurement regardless if it's mm, m or cm

Excelchat Expert
13/08/2018 - 12:57

That would be extremely hard if not possible. Cause you need to know the length of text you would be extracting. And it is doable if there is a pattern.

User
13/08/2018 - 12:58

it is just like this

User
13/08/2018 - 12:58

Thickness (value) measurement

User
13/08/2018 - 12:58

it is always like this

Excelchat Expert
13/08/2018 - 12:58

What I meant is when I said "mm" at the end, we know that we want to extract everything between "thickness" and "mm".

Excelchat Expert
13/08/2018 - 12:58

But if it is not "mm" then the whole thing changes.

Excelchat Expert
13/08/2018 - 12:59

Excel can't automatically identify what is used for measurement.

User
13/08/2018 - 12:59

ah

User
13/08/2018 - 12:59

could you try just M? as CM, MM & M all end with M

Excelchat Expert
13/08/2018 - 01:00

Nah, just "M" can be used countless of times in the specification. It can even be the very first letter of specification.

User
13/08/2018 - 01:00

ok

Excelchat Expert
13/08/2018 - 01:00

So, again, not doable.

User
13/08/2018 - 01:01

is it possible you could use all three measurements (im not sure how in excel this works) but in coding it's like "if cm, mm or m return value

Excelchat Expert
13/08/2018 - 01:01

But give me some time, Since we have time remaining, I want to try something.

User
13/08/2018 - 01:01

ok yessir

Excelchat Expert
13/08/2018 - 01:01

Thanks.

Excelchat Expert
13/08/2018 - 01:02

How many different measurements can be there? And what are those?

User
13/08/2018 - 01:02

3 - mm, cm, m

Excelchat Expert
13/08/2018 - 01:03

I can see "in" after Width in 3rd example.

User
13/08/2018 - 01:03

ah damn

User
13/08/2018 - 01:04

inches also, let me see all the measurements and get back to yo

User
13/08/2018 - 01:04

you

Excelchat Expert
13/08/2018 - 01:04

Okay..

User
13/08/2018 - 01:05

Ok sir, there are only 4 measurements that I can see

User
13/08/2018 - 01:05

mm, cm, m & in

User
13/08/2018 - 01:05

millimetres, centimeters, metres & inches

Excelchat Expert
13/08/2018 - 01:06

Got it.

Excelchat Expert
13/08/2018 - 01:06

I'm trying something, please stay with me.

User
13/08/2018 - 01:06

yessir

User
13/08/2018 - 01:16

I am currently working I will be back in 5 mins

Excelchat Expert
13/08/2018 - 01:17

Okay, no problem. I'm working on this too.

User
13/08/2018 - 01:17

ok

User
13/08/2018 - 01:20

is it possible if i take too much time you could increase it?

User
13/08/2018 - 01:21

worst case scenario i take to long let's continue with this on my email, email me @ himanshujsethia@gmail.com

User
13/08/2018 - 01:21

will be back soon

Excelchat Expert
13/08/2018 - 01:21

Okay.

Excelchat Expert
13/08/2018 - 01:25

At this point I found a solution.

Excelchat Expert
13/08/2018 - 01:30

=MID(MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)),10,SEARCH("W",MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)))-10)

Excelchat Expert
13/08/2018 - 01:30

For Thickness

Excelchat Expert
13/08/2018 - 01:30

=MID(MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)),SEARCH("Width",MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)))+5,SEARCH("L",MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)))-SEARCH("Width",MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)))-5) for Width

Excelchat Expert
13/08/2018 - 01:31

=RIGHT(MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)),LEN(MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)))-SEARCH("Length",MID(A2,SEARCH("Thickness",A2),SEARCH("Grading",A2)-SEARCH("Thickness",A2)))-6) for Length

Excelchat Expert
13/08/2018 - 01:34

The session will end now, have a great day.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*