Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina

i dont know whether you can try this

=SUBSTITUTE(A1,"'","",1)

experiment on this
=================
Martina wrote in message
...
Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina

would it be difficult to modify your formulas to convert the number to text?
For example if

A1 = 04152365221 (without the single quote)

you could refer to it by TEXT(A1,"00000000000") instead of just A1.


As a generic example, VLOOKUP(A1, Arg2, Arg3, Arg4) would be
VLOOKUP(TEXT(A1,"00000000000"), Arg2, Arg3, Arg4).




"Martina" wrote:

Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina

Hi!

'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221

What format is that? Is it formatted as TEXT so it keeps the leading 0 or is
is a custom number format 00000000000?

Could be the imported data has unseen characters such as spaces, line feeds,
line breaks, etc.

If both values have the same format try comparing the length of each string.
If this data is imported monthly, why would the format change from month to
month?

Biff

"Martina" wrote in message
...
Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina

Hi Biff,

Thank you for taking the time to consider my problem. The format does
not change monthly, the database section has had a recent system change.
When I needed the information the inverted commas were present next to
the student numbers. After the system change,one month later the system
has done away with the inverted commas leaving me in a pickle. I
believe the system is now stable.

It could be unseen characters, how do I compare the length of the
string. To me they look the same except for the inverted comma. How do
I tell if it is formatted as text? I assume that is what the inverted
comma means.

I think the system will stay now without the inverted commas so I need
to get my data into this format.

kind regards

Martina

Biff wrote:
Hi!


'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221


What format is that? Is it formatted as TEXT so it keeps the leading 0 or is
is a custom number format 00000000000?

Could be the imported data has unseen characters such as spaces, line feeds,
line breaks, etc.

If both values have the same format try comparing the length of each string.
If this data is imported monthly, why would the format change from month to
month?

Biff

"Martina" wrote in message
...

Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina

Hi!

Is the "inverted comma" visible in the cell or does it only show up in the
formula bar?

If it only shows up in the formula bar then that means the value is TEXT.

If it is visible in the cell then you could try EditReplace.

Select the range of cells that have that inverted comma.
GotoEditReplace
Find what: '
Replace with: nothing, leave this blank
Replace All

How do I tell if it is formatted as text?

Unless you have changed formats, TEXT will default align left and NUMBERS
default align right.

To compare length:

Suppose the value with the inverted comma is in cell A1:

=LEN(A1)

And compare that result to the length of the other value.

If the inverted comma is just a text format apostrophe, that will not be
counted in the length.

Biff

"Molly" wrote in message
...
Hi Biff,

Thank you for taking the time to consider my problem. The format does not
change monthly, the database section has had a recent system change. When
I needed the information the inverted commas were present next to the
student numbers. After the system change,one month later the system has
done away with the inverted commas leaving me in a pickle. I believe the
system is now stable.

It could be unseen characters, how do I compare the length of the string.
To me they look the same except for the inverted comma. How do I tell if
it is formatted as text? I assume that is what the inverted comma means.

I think the system will stay now without the inverted commas so I need to
get my data into this format.

kind regards

Martina

Biff wrote:
Hi!


'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221


What format is that? Is it formatted as TEXT so it keeps the leading 0 or
is is a custom number format 00000000000?

Could be the imported data has unseen characters such as spaces, line
feeds, line breaks, etc.

If both values have the same format try comparing the length of each
string. If this data is imported monthly, why would the format change
from month to month?

Biff

"Martina" wrote in message
...

Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina

Hi Biff,

Thank you. This is why I have learnt so much from this newsgroup!!
a)The inverted comma appears in the cell
b) I did a find and replace but no luck.
c)The length function however showed me that the new database system places
an extra space at the end of the number.
d)At least now I know I'm not going mad.

As soon as I removed the extra space the sheets were talking to eachother.
If I can reformat my data in this way perhaps they will talk.

Thanks again.

Martina

"Biff" wrote:

Hi!

Is the "inverted comma" visible in the cell or does it only show up in the
formula bar?

If it only shows up in the formula bar then that means the value is TEXT.

If it is visible in the cell then you could try EditReplace.

Select the range of cells that have that inverted comma.
GotoEditReplace
Find what: '
Replace with: nothing, leave this blank
Replace All

How do I tell if it is formatted as text?

Unless you have changed formats, TEXT will default align left and NUMBERS
default align right.

To compare length:

Suppose the value with the inverted comma is in cell A1:

=LEN(A1)

And compare that result to the length of the other value.

If the inverted comma is just a text format apostrophe, that will not be
counted in the length.

Biff

"Molly" wrote in message
...
Hi Biff,

Thank you for taking the time to consider my problem. The format does not
change monthly, the database section has had a recent system change. When
I needed the information the inverted commas were present next to the
student numbers. After the system change,one month later the system has
done away with the inverted commas leaving me in a pickle. I believe the
system is now stable.

It could be unseen characters, how do I compare the length of the string.
To me they look the same except for the inverted comma. How do I tell if
it is formatted as text? I assume that is what the inverted comma means.

I think the system will stay now without the inverted commas so I need to
get my data into this format.

kind regards

Martina

Biff wrote:
Hi!


'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221


What format is that? Is it formatted as TEXT so it keeps the leading 0 or
is is a custom number format 00000000000?

Could be the imported data has unseen characters such as spaces, line
feeds, line breaks, etc.

If both values have the same format try comparing the length of each
string. If this data is imported monthly, why would the format change
from month to month?

Biff

"Martina" wrote in message
...

Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina

Hi Biff,

I found it!! The entry is a text entry followed by a small circle which I
could only see when I brought it into Word. It is like a degree symbol as in
degrees centigrade. One of these invisibly follows each number. This was
enough to send me around the twist!!!!!

0416685667º

Thanks again

Martina

"Martina" wrote:

Hi Biff,

Thank you. This is why I have learnt so much from this newsgroup!!
a)The inverted comma appears in the cell
b) I did a find and replace but no luck.
c)The length function however showed me that the new database system places
an extra space at the end of the number.
d)At least now I know I'm not going mad.

As soon as I removed the extra space the sheets were talking to eachother.
If I can reformat my data in this way perhaps they will talk.

Thanks again.

Martina

"Biff" wrote:

Hi!

Is the "inverted comma" visible in the cell or does it only show up in the
formula bar?

If it only shows up in the formula bar then that means the value is TEXT.

If it is visible in the cell then you could try EditReplace.

Select the range of cells that have that inverted comma.
GotoEditReplace
Find what: '
Replace with: nothing, leave this blank
Replace All

How do I tell if it is formatted as text?

Unless you have changed formats, TEXT will default align left and NUMBERS
default align right.

To compare length:

Suppose the value with the inverted comma is in cell A1:

=LEN(A1)

And compare that result to the length of the other value.

If the inverted comma is just a text format apostrophe, that will not be
counted in the length.

Biff

"Molly" wrote in message
...
Hi Biff,

Thank you for taking the time to consider my problem. The format does not
change monthly, the database section has had a recent system change. When
I needed the information the inverted commas were present next to the
student numbers. After the system change,one month later the system has
done away with the inverted commas leaving me in a pickle. I believe the
system is now stable.

It could be unseen characters, how do I compare the length of the string.
To me they look the same except for the inverted comma. How do I tell if
it is formatted as text? I assume that is what the inverted comma means.

I think the system will stay now without the inverted commas so I need to
get my data into this format.

kind regards

Martina

Biff wrote:
Hi!


'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221


What format is that? Is it formatted as TEXT so it keeps the leading 0 or
is is a custom number format 00000000000?

Could be the imported data has unseen characters such as spaces, line
feeds, line breaks, etc.

If both values have the same format try comparing the length of each
string. If this data is imported monthly, why would the format change
from month to month?

Biff

"Martina" wrote in message
...

Could someone help me with a data format issue. I am importing student
numbers from a university database which come into my sheet as:

'04152365221

This will not speak to older data (from last month!) which is of the
format:

04152365221

I have tried:
Removing the inverted comma.
Doing the multiply by 1
Changing both to General format
Have used the Text to Columns feature.

I can't seem to achieve a common ground. It only works if I physically
retype each number in again. This is really not a viable option.

Any help would be greatly appreciated

regards
Martina