Formula to move part of a value to a new cell

[forestavekids]

Hi All..

Trying to move the value of Cell A1 containing "2780A21151-99-P6" tp cell B1 to excluding the first two values "27" and all values after the first "-". So cell A1 is "2780A21151-99-P6" and it should look like this in cell B2 "80A21151-"

Can anyone tell me the formula for this?

[Crooza]

Try this

=LEFT(RIGHT(A1,LEN(A1)-2),FIND("-",RIGHT(A1,LEN(A1)-2)))

[TheN]

Rather than using a formula, you can copy paste the value over, and use text to columns with fixed width, and divide after the first 2 numbers.

Depending on how massive your data is, this method can save a lot of space (it is also "easier" once you understand how to do it).

[Tony Valko]

Here's another one...

=MID(A1,3,FIND("-",A1)-2)