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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,025
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


Excel 2007
ABC
1Breakages(%)Breakages(%) - AfterAfter
2680/129600680129600
31120/1296001120129600
41200/1332001200133200
51120/1296001120129600
6680/129600680129600
7760/133200760133200
8920/259200920259200
9504/129600504129600
10416/129600416129600
Sheet4


Regards

Dave
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
273
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,025
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
54,389
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
273
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,282
Messages
5,836,404
Members
430,425
Latest member
xlsee

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