CODEWITHSUNDEEP
excel
HarveyFrench
HarveyFrench

Reputation: 4568

How can I take numeric values in a range of cells and convert them to be text values

Consider: Paste special will let you convert text in a range to numbers by pasting another cell with value of 1 and using the values and multiplying options, but what if I want to do the reverse.

i.e. take a value stored as 123 and store it as a textual value '123

The actual value stored needs to be changed not just the way it is displayed.

I’m working with telephone numbers, and found a need to merge two lists with numbers into a single list that for lookup purposes needed to be stored in text order.

So my area codes look like this (the numbers are stored as text)

ColumnA   ColumnB 
0113   Leeds
0113   Leeds
0114   Sheffield
0114   Sheffield
0115   Nottingham
0116   Leicester

My list of international dialling codes look like this (the number was stored as a number)

7   Turkmenistan
20   Egypt
27   South Africa
30   Greece
31   Netherlands (Holland)
32   Belgium
33   France

When I pasted the second list at the end of the first list then sorted aboth the lists as one, the numbers in the second list were all together at the top as the second is all numerics.

What if I want to change the second list to be text values! Help!

Note that if I change the first list text values cells to be numbers I will loose the "0" as the first character. So I need them all text.

PS. A code solution would be good as well as a how to use the Excel GUI to do it, if anyone knows.

PPS. the above description is only a representative example of the issue, the problem re-occurs a lot when dealing with phone numbers and the export and import of data data. Excel seems to like converting numbers to be text values for some reason).

Harvey

Upvotes: 1

Views: 393

Answers (2)

HarveyFrench
HarveyFrench

Reputation: 4568

I've written this VBA, which I think is a "Ultimate" answer to my question.

Public Function ConvertValueToHaveTextDataType(Avalue As Variant) As String

    ConvertValueToHaveTextDataType = CStr(Avalue)

End Function

PS. for a fuller explanation see SO question 28000600 here

Upvotes: 0

user4039065
user4039065

Reputation:

I would recommend not turning the second table into text values. Change the first table into numbers and use a custom number format of 0000 to display the numbers as 4 digits with leading zeroes.

  1. Set the entire column to a General format.
  2. Use Data ► Data Tools ► Text to Columns. One the first screen pick Fixed width then click Finish.
  3. The columns should all be right-aligned numbers in the General format. Select all of the numbers in the column and tap Ctrl+1. When the Format Cell dialog opens, go to the Numbers tab and pick Custom from the list down the left. Use 0000 for the Type: and click OK in the lower right.

When you're done you should have a full column of numbers displayed as four digits with leading zeroes (if necessary). Sorting should be no problem.

Upvotes: 2

Related Questions