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.
