Excel - Excel - cut out first character of string

Asked By Ultsun1 on 02-Oct-07 02:44 PM
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




Ron Coderre replied on 02-Oct-07 02:52 PM
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:
...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)
Gord Dibben replied on 02-Oct-07 02:57 PM
=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
Martin replied on 03-Oct-07 02:45 AM
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