I saw somebody else extract part of a cell - can you help?

A

AngrieWoman

In my list, there are inventory numbers in columns, like such:

023012977500000
023015877700000

What I would like to do is somehow get rid of the first set of
numbers, the "0230" set, and also the end set, the zeros. I saw
somebody at work do something similiar once, with a command or
function...it looked like he was telling it to count over X places,
then get the rest of the numbers maybe?

Any suggestions?
Tia,
A
 
T

Trevor Shuttleworth

A

use MID. If your data is in cell A1, then in another cell on the same row,
say B1, put:

=MID(A1,5,LEN(A1)-9)

Regards

Trevor
 
K

Ken Wright

If all the data is EXACTLY the same length as per your example then simply
select it all and use Data / text To Columns / Fixed Width and put the breaks
where you want them. You can then choose whether or not to import the columns
of data around your required values, but either way they get separated.

If different lengths but the items to be removed at the start and beginning are
all the same length then take a look at the functions

LEFT()
RIGHT()
MID()

A1 = ABCD123456EFGHIJ
B1 = =MID(A1,5,LEN(A1)) = 123456EFGHIJ (You want to lose first 4, so hence 5 is
starting point)
C1 = =LEFT(B1,LEN(B1)-6) = 123456 (You want to lose last 6 so hence -6 )
 
A

AngrieWoman

Both of those things will work! Thanks so much. I really don't know
what the "MID" function does, so I'm off to toy around a bit.

Thanks to both of you!

Angela
 
K

Ken Wright

=LEFT(reference,x) will give you x number of characters from the Left of the
string, so for example:-

If A1 contains, QWERTYUIOP123 then the following formula


=LEFT(A1,5) gives you the first 5 characters from the left, namely "QWERT"

=RIGHT(A1,7) gives you the first 7 characters from the right, namely "UIOP123"

=MID((A1,3,4) gives you the first 4 characters starting from the 3rd character,
namely "ERTY"
 
Top