copy numbers and text to separate colums

higguns

New Member
Joined
Mar 10, 2018
Messages
9
hello,

I have a a file which has info such as qty and description of items in the same column.

I need to separate the info into two different columns or at minimum copy just the starting numbers to a separate column.

The info is usually in this format with the info needing to be extracted or copied to another cell highlighted in red:

QTY - DESCRIPTION
e.g.

100 - pull stations
43 - bells
2 - 9V batteries (floors 2-6)

As you can see above there are different amounts of characters making up the numbers needing to be copied and there are other numbers in the folowing text that don't need to be copied. Need to do this to hundreds of lines and a formula or macro would be most helpful instead of having to do it manually.

I've searched and searched but can't find a solution.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi,

How about this:


Book1
ABC
1QTYDESCRIPTION
2100 - pull stations100pull stations
343 - bells43bells
42 - 9V batteries (floors 2-6)29V batteries (floors 2-6)
Sheet168
Cell Formulas
RangeFormula
B2=LEFT(A2,FIND("-",A2)-1)+0
C2=TRIM(MID(A2,FIND("-",A2)+1,255))


B2 and C2 formula copied down.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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
Back
Top