Formula to copy partial content of a cell?

Mar 12th, 2002, 09:19 AM

Question:
I am wondering is there an excel formula to copy one partial content of a cell to another cell?
eg. Copy 90 from Weight-90 in a cell to another cell
Thanks in advance

Aladin Akyurek · Mar 12th, 2002, 09:28 AM

Quote:

On 2002-03-12 08:19, Anonymous wrote:
Question:
I am wondering is there an excel formula to copy one partial content of a cell to another cell?
eg. Copy 90 from Weight-90 in a cell to another cell
Thanks in advance

In B1 enter: =RIGHT(A1,2)

where A1 houses Weight-90, would result in 90.

Is that what you're looking for?

Robin · Mar 12th, 2002, 09:28 AM

If you want to just pull off the last two characters from a cell then you would use:

=right(a1,2) where a1 is the cell in question.

If it more complicated than that, let me know.

Robin

Mar 12th, 2002, 09:36 AM

If I have a text
Weight = 60kg
How can I copy just the number 60(no kg included)?
Thanks in advance

Dave Hawley · Mar 12th, 2002, 09:47 AM

Just select the cell, push F2, higlight it and copy.

Mar 12th, 2002, 09:55 AM

I really need formula to do this
because I have about 40000 of cells. If I do it one by one, it will take forever to get it done

Dave Hawley · Mar 12th, 2002, 09:59 AM

The can you use has has been suggested:

=LEFT(D14,2)*1

Will return a real number.

If not is the number always followed by "kg"? If so use:

=SUBSTITUTE(D14,"kg","")*1

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-03-12 09:01 ]

Dave Hawley · Mar 12th, 2002, 10:06 AM

Or even the Text to columns wizard under Data and using Delimited with "Other" and just "k" delimiter

NateO · Mar 12th, 2002, 10:20 AM

If your entries always look like:

Weight = 60kg or Weight = 103kg

You can use the following and it will separate the number at two to three numbers:

=IF(SEARCH("k",A1)=13,MID(A1,10,3),MID(A1,10,2))+0

The formula is set up for the string in cell a1, change and copy appropriately.

Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-12 09:20 ]

NateO · Mar 12th, 2002, 10:34 AM

Might as well go 4 deep (weight from 1 to 4 didgits) at this point:

=IF(SEARCH("k",A1)=11,MID(A1,10,1)+0,IF(SEARCH("k",A1)=12,MID(A1,10,2)+0,IF(SEARCH("k",A1)=13,MID(A1,10,3)+0,MID(A1,10,4))+0))

Hope this helps. Cheers,

Nate

[ This Message was edited by: NateO on 2002-03-12 09:41 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 09:19 AM	#1
Guest Posts: n/a	Question: I am wondering is there an excel formula to copy one partial content of a cell to another cell? eg. Copy 90 from Weight-90 in a cell to another cell Thanks in advance

Mar 12th, 2002, 09:28 AM	#3
Robin New Member Join Date: Feb 2002 Posts: 16	If you want to just pull off the last two characters from a cell then you would use: =right(a1,2) where a1 is the cell in question. If it more complicated than that, let me know. Robin

Mar 12th, 2002, 09:36 AM	#4
Guest Posts: n/a	If I have a text Weight = 60kg How can I copy just the number 60(no kg included)? Thanks in advance

Mar 12th, 2002, 09:47 AM	#5
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Just select the cell, push F2, higlight it and copy.

Mar 12th, 2002, 09:55 AM	#6
Guest Posts: n/a	I really need formula to do this because I have about 40000 of cells. If I do it one by one, it will take forever to get it done

Mar 12th, 2002, 09:59 AM	#7
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	The can you use has has been suggested: =LEFT(D14,2)1 Will return a real number. If not is the number always followed by "kg"? If so use: =SUBSTITUTE(D14,"kg","")1 _________________ Kind Regards Dave Hawley OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-03-12 09:01 ]

Mar 12th, 2002, 10:06 AM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Or even the Text to columns wizard under Data and using Delimited with "Other" and just "k" delimiter

Mar 12th, 2002, 10:20 AM	#9
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	If your entries always look like: Weight = 60kg or Weight = 103kg You can use the following and it will separate the number at two to three numbers: =IF(SEARCH("k",A1)=13,MID(A1,10,3),MID(A1,10,2))+0 The formula is set up for the string in cell a1, change and copy appropriately. Cheers, Nate [ This Message was edited by: NateO on 2002-03-12 09:20 ]

Mar 12th, 2002, 10:34 AM	#10
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Might as well go 4 deep (weight from 1 to 4 didgits) at this point: =IF(SEARCH("k",A1)=11,MID(A1,10,1)+0,IF(SEARCH("k",A1)=12,MID(A1,10,2)+0,IF(SEARCH("k",A1)=13,MID(A1,10,3)+0,MID(A1,10,4))+0)) Hope this helps. Cheers, Nate [ This Message was edited by: NateO on 2002-03-12 09:41 ]