how to cut part of a text from one cell and automatically paste itonto another cell

[Sonja]

Hello,

Please see below example: I have column A1 which has information
CHI090169094 BKG# 19802749 and I want to extract BKG# 19802749 and
put it into column B1 and delete the information that was in A1 for
BKG# 19802749.
Can someone advise how I can do this. I tried using the formula =LEFT
(A1, 13) and it captures CHI090169094 and pastes into B1 column. But
That's not what I'm looking for. I would like for the formula to
capture BKG# 19802749 from A1 and put it into column B1 and delete
the information that was copied from cell A1.

A1 B1
CHI090169094 BKG# 19802749 BKG# 19802749
CHI090114574 BKG# 18133600
CHI090122224 BKG# 16469485

Thanks,
Sonia

 

[L. Howard Kittle]

Right(a1,13) should get you there.

HTH
Regards,
Howard

 

[Sonja]

Howard,

Thanks that helps me get the text and insert it to B1 but once I get
the text I want to remove it from A1. Can you advise what other
function I need to add to it.

Tks,
Sonia

 

[NDBC]

Sonja,

You can not use a formula to overwrite the data it is using. YOu would need
vba code to do this. All you could do is put Right(a1,13) in B1 to get BKG#
19802749 and left(A1,12) in C1 to get CHI090169094. Hope this helps

 

[Gord Dibben]

Data>Text to Columns>Fixed Width.

Drop a split at the first space.

Assuming your data is consistent as your example.


Gord Dibben MS Excel MVP

 

[L. Howard Kittle]

Try this...

In a column use Left(a1,13)
In another adjacent column use Right(a1,13)

Now copy the two columns and Past Special > Values > OK back into column A
and B.

Regrds,
Howard

 

[Sonja]

Try this...

In a column use Left(a1,13)
In another adjacent column use Right(a1,13)

Now copy the two columns and Past Special > Values > OK back into column A
and B.

Regrds,
Howard









- Show quoted text -

Thanks you all for your help. That will work.