Remove "x" number of characters from left of cell

ChrisRitchie

New Member
Joined
Nov 15, 2011
Messages
47
Hi all

I would like to copy the 1st 3 letters from the left of a cell and then remove these 3 characters from that cell too.

I have used =LEFT(B3,3) to copy the characters but I don't know how to remove those 3 characters from the cell, B3.

I appreciate any guidance you could provide.

Kind regards
Chris
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

ChrisRitchie

New Member
Joined
Nov 15, 2011
Messages
47
Ahh, yes. Good thinking, thank you!

To complicate it further, column B has 300+ records all with a varying degree of the following text:
173 ISDN BT
162 TIE Virgin
2011 T1 BTW

I would like to separate all 3 bits of data into 3 columns. The only common thing about all cells is that the text is separated by a space in column B cells.

Thanks
Chris
 

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Try Text to Columns with a space delimiter. Which version of excel are you using?
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,807
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi all

I would like to copy the 1st 3 letters from the left of a cell and then remove these 3 characters from that cell too.

I have used =LEFT(B3,3) to copy the characters but I don't know how to remove those 3 characters from the cell, B3.

I appreciate any guidance you could provide.

Kind regards
Chris

I'm partial to MID function method myself. :)

=MID(A1,4,255)
 

ChrisRitchie

New Member
Joined
Nov 15, 2011
Messages
47
Thanks Mike

I am using Excel 2010 on Win7.

This is extremely handy, but to be even more complicated, some of the names in the column B are further separated by spaces.
eg:
173 ISDN BT
162 TIE Virgin
175 2MB BT Wholesale
762 ISDN A B C D

Can you still help, otherwise I will use a combination?

Thanks for your assistance!
Chris
 

ChrisRitchie

New Member
Joined
Nov 15, 2011
Messages
47

ADVERTISEMENT

One other thing, can I reverse the the text to column ?
IE: after I have removed some data, can I reverse the action?
eg: 173 ISDN BT TEN Five = 173, ISDN, BT, TEN, Five, remove 173 and ISDN, and combine BT, TEN, Five back into a single cell?

Thanks again!
 
Last edited:

ChrisRitchie

New Member
Joined
Nov 15, 2011
Messages
47
ahhh man, no offense, such obvious answers, but it takes an intelligent one to think about them.

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

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
Top