How to transfer a specific number to another cell

jumada06

New Member
Joined
Feb 17, 2016
Messages
21
Sir,

I have this data from our hatchery unit and I need only the number of damaged eggs without the number of actual eggs delivered:

Breakages(%)
680/129600
1120/129600
1200/133200
1120/129600
680/129600
760/133200
920/259200
504/129600
416/129600

There are thousands of this info and I find it very inconvenient to copy manually the damaged eggs to another cell. I used the Left and Right formula but does not give me the result I want.

Your prompt assistance is highly appreciated.

Regards,
Dio
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Hi

Have you tries Text-To-Columns? Data | Text to Columns | Delimited -> Next | Other (insert / forward slash) -> Finish

Result before and after would look like this

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Breakages(%)</td><td style=";">Breakages(%) - After</td><td style=";">After</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">680/129600</td><td style="text-align: right;color: #333333;;">680</td><td style="text-align: right;;">129600</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">1120/129600</td><td style="text-align: right;color: #333333;;">1120</td><td style="text-align: right;;">129600</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;;">1200/133200</td><td style="text-align: right;color: #333333;;">1200</td><td style="text-align: right;;">133200</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;;">1120/129600</td><td style="text-align: right;color: #333333;;">1120</td><td style="text-align: right;;">129600</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;;">680/129600</td><td style="text-align: right;color: #333333;;">680</td><td style="text-align: right;;">129600</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;;">760/133200</td><td style="text-align: right;color: #333333;;">760</td><td style="text-align: right;;">133200</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;;">920/259200</td><td style="text-align: right;color: #333333;;">920</td><td style="text-align: right;;">259200</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;;">504/129600</td><td style="text-align: right;color: #333333;;">504</td><td style="text-align: right;;">129600</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #333333;;">416/129600</td><td style="text-align: right;color: #333333;;">416</td><td style="text-align: right;;">129600</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br />

Regards

Dave
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
266
When you say that you've tried left / right and it didn't work - in what way did it fail?
 

jumada06

New Member
Joined
Feb 17, 2016
Messages
21
When you say that you've tried left / right and it didn't work - in what way did it fail?

As you are aware the number of damaged eggs is not consistent in number upon delivery. So if I put =LEFT(A1,3) it will give only the first three digits.

Regards,
Dio
 

jumada06

New Member
Joined
Feb 17, 2016
Messages
21

ADVERTISEMENT

Nope! I'll try for I haven't done the text to column formula yet.

Thank you,
Dio
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

It sounds like text to columns would be easier but if you must have a formula then =LEFT(A2,FIND("/",A2)-1)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Nope! I'll try for I haven't done the text to column formula yet.

Thank you,
Dio
Text to Columns is not a formula, it's a quite quick manual process. In a copy of your workbook, try this.

1. Select the column of data by clicking its heading label.
2. On the Data ribbon tab click Text to Columns -> Delimited -> Next -> Type a / in the 'Other' box -> Finish
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
266
It sounds like text to columns would be easier but if you must have a formula then =LEFT(A2,FIND("/",A2)-1)

This is the formula which I would use with only one addition: =LEFT(A1,FIND("/",A1)-1)*1 which will ensure that the return is a number. If there are cells which may not contain a "/" you could enhance it further with IFERROR(LEFT(A1,FIND("/",A1)-1)*1,"") to return a blank if the LEFT formula would have generated and error.

Regards

Miles
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top