Excel - cut out first character of string

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ultsun1 - 02 Oct 2007 19:44 GMT

Greetings,

I would like to cut the first character in each cell of column B.

-Example the cell may contain 6008, I would like to change that to
008. Also, cell may contain G302; this should be just 302.

Is there a function or macro that i can use to do this?

I have found a useful function: =RIGHT(B2,LEN(B2)-1), however it
requires a circular reference to work.

I also did this: =RIGHT(6004,LEN(6004)-1) which doesn't require a
circular reference, however i need to use this function throughout 10
spreadsheets.

Can someone help me with this function or module?

Thanks,

ultsun1

Reply to this Message

Ron Coderre - 02 Oct 2007 19:52 GMT

If you actually want to remove the 1st character from the cells...
try this:

Select the single-column range of cells.
From the Excel Main Menu:
<Data><Text-to-Columns>
...Check: Fixed width
...Click [Next]
...Click after the 1st char to insert a break point
...Click [Next]
Click the 1st col....Check: Do not import column (skip)
Click the 2nd col....Check: Text
Click [Finish]

That will remove the 1st char from each cell and convert the remainder to
text.

Is that something you can work with?

--------------------------
Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> Greetings,
>
[quoted text clipped - 17 lines]
>
> ultsun1

Reply to this Message

Gord Dibben - 02 Oct 2007 19:57 GMT

=RIGHT(B2,LEN(B2)-1) does not create a circular reference unless entered in B2

If entered in C2 will return the trucated string.

Gord Dibben MS Excel MVP

>Greetings,
>
[quoted text clipped - 17 lines]
>
>ultsun1

Reply to this Message

MartinW - 03 Oct 2007 07:45 GMT

And one more,

=MID(A1,2,5) or
=MID(A1,2,10) or
=MID(A1,2,50)
just make the last value greater than
the most number of characters you
will have in a cell.

HTH
Martin

> Greetings,
>
[quoted text clipped - 17 lines]
>
> ultsun1

Reply to this Message