Excel is converting my alphanumeric text to scientific notation

[Richard Tomkinson]

I am having a problem with excel. I am importing text from another spreadsheet with thousands of entries. The column of concern is room numbers in the format of:

Building Name/Floor Level/Room Number
(i.e. Tower/03/03B14)

The room numbers are all alphanumeric. I need the room numbers broken out separately. Most are fine, but when they contain an 'E', Excel sees the the alphanumeric text as scientific notation no matter how the cells are formatted. So I can't use the the Text-to-Columns tools which would be the easiest way to do this. The different building names, floor levels and room numbers have differing numbers of digits as well, so a straight forward LEFT, MID or RIGHT function doesn't work either.

Any help would be appreciated.

 

[GS]

It happens that Richard Tomkinson formulated :

I am having a problem with excel. I am importing text from another
spreadsheet with thousands of entries. The column of concern is room numbers
in the format of:

Building Name/Floor Level/Room Number
(i.e. Tower/03/03B14)

The room numbers are all alphanumeric. I need the room numbers broken out
separately. Most are fine, but when they contain an 'E', Excel sees the the
alphanumeric text as scientific notation no matter how the cells are
formatted. So I can't use the the Text-to-Columns tools which would be the
easiest way to do this. The different building names, floor levels and room
numbers have differing numbers of digits as well, so a straight forward LEFT,
MID or RIGHT function doesn't work either.

Any help would be appreciated.

Try formatting the cells as 'Text'. This can be done during the import
process.

 

[Bruce Sinclair]

It happens that Richard Tomkinson formulated :


Try formatting the cells as 'Text'. This can be done during the import
process.

... and if the format is consistent (ie the parts are separated by "/"), you
can use the find function together with the left, right and mid functions to
pick out the bits between the /s ).