Copy part of content from one celle to another

  • Thread starter Thread starter EK
  • Start date Start date
E

EK

In column D12 to D2000 reference numbers are filled in manually - these look
like this FTFP0106936 or FTFP1206937

I need to get the first two digits - in the expl. 01 or 12 shown in another
column f.ex. column AP12 to AP2000

The two digits are equal to the number of month - in this case january and
december. The amount of rows with references with the various months can
differ.

I hope somebody has a solution to this.

Erik
 
If those reference numbers always begin with 4 characters, then you
can put this in AP12:

=MID(D12,5,2)

and then copy it down to AP2000. If you want the digits as proper
numbers you can do this:

=MID(D12,5,2)*1

Did you want the months?

Hope this helps.

Pete
 
Hi,

If you text can be of variable length then use the following array formula

=MID(A1,MATCH(FALSE,ISERR(-MID(A1,ROW(1:20),1)),0),2)

Array - enter it by press Shift+Ctrl+Enter

If this is helpful, please click the Yes button.

Cheers,
Shane Devenshire
 
"Pete_UK" <[email protected]> skrev i meddelelsen
If those reference numbers always begin with 4 characters, then you
can put this in AP12:

=MID(D12,5,2)

and then copy it down to AP2000. If you want the digits as proper
numbers you can do this:

=MID(D12,5,2)*1

Did you want the months?

Hope this helps.

Pete

In column D12 to D2000 reference numbers are filled in manually - these
look
like this FTFP0106936 or FTFP1206937

I need to get the first two digits - in the expl. 01 or 12 shown in
another
column f.ex. column AP12 to AP2000

The two digits are equal to the number of month - in this case january and
december. The amount of rows with references with the various months can
differ.

I hope somebody has a solution to this.

Erik
Click to expand...

Hello Pete,
yes, it works - but now I got a new problem.

If Column D is not yet filled out, your formula gives the answer #value!.
This is normal, but it disturbs calculations in other cells (the value from
cells in AP are used for these calculations).

Is it possible to keep cells in column AP blank as long as D is still empty
?

Erik
 
Erik,

You can amend the formula to this:

=IF(D12="","",MID(D12,5,2)*1)

so if D12 is blank the formula returns a blank.

Hope this helps.

Pete
 
"Pete_UK" <[email protected]> skrev i meddelelsen
Erik,

You can amend the formula to this:

=IF(D12="","",MID(D12,5,2)*1)

so if D12 is blank the formula returns a blank.

Hope this helps.

Pete

Thanks a lot Pete,

everything runs pefectly now

Erik
 
Back
Top