Convert to Text

[BazLondon]

Greetings

I cannot find a Function in EXCEL to carry out a specific required task and if there isn't one I'm surprised it hasn't been supplied as standard.

I have a spreadsheet in which one of the columns contains cells that have been formatted differently, for example some are 'text', some are 'general' and some are 'numeric' (with different numbers of decimal places).

Incidentally the data represents version numbers of various software programs but the originator of the spreadsheet was inconsistent with the data entry (it should all have been entered as text).

I need a Function to convert each of the displayed (ie formatted not internally stored) cell values to a text string (to produce an adjacent column of displayed values using copy/paste).

If, as I suspect, this involves VBA then I confess I am lost for a start so go easy on me in words of one syllable!

If it is possible to create such a Function, is it feasible for my version of EXCEL (2007) to be customised to include it with all the other Functions for future use as I think it will prove to be very useful?

Many thanks

BazLondon

 

[Brianwarnock]

Assuming that you don't want to convert the original column to TEXT formatting then select and format the new column as Text and then when you copy and paste select Match destination formatting.

Brian

 

[HaHoBe]

Hi, BazLondon,

if you really want to use VBA the following macros should do the trick:

Sub ConvertToStrings()
Dim lngLastRow As Long
Dim lngCounter As Long
Const cstrSourceCol As String = "A"
Const cstrResultCol As String = "D"

Application.ScreenUpdating = False
lngLastRow = Cells(Rows.Count, cstrSourceCol).End(xlUp).Row
For lngCounter = 1 To lngLastRow
  Cells(lngCounter, cstrResultCol).Value = "'" & Cells(lngCounter, cstrSourceCol).Text
Next lngCounter
Application.ScreenUpdating = True
End Sub

Sub ConvertToStringsPara(strSource As String, strAim As String)
Dim lngLastRow As Long
Dim lngCounter As Long

Application.ScreenUpdating = False
lngLastRow = Cells(Rows.Count, strSource).End(xlUp).Row
For lngCounter = 1 To lngLastRow
  Cells(lngCounter, strAim).Value = "'" & Cells(lngCounter, strSource).Text
Next lngCounter
Application.ScreenUpdating = True
End Sub

Sub CallUp()
ConvertToStringsPara "A", "F"
End Sub

Ciao,
Holger

 

[BazLondon]

Thanks to Holger and Brian for your quick responses.

Holger. No I don't really want to use VBA if I can help it. Your solution may well work but as I indicated in my original post the VBA is a foreign language to me. Even if I were to blindly use your suggested code I wouldn't have a clue how to implement it in EXCEL. Thanks anyway. I'll show it to a family member who knows VB Script so he may be able to help.

Brian. Regrettably your solution does not work. If, for example, I have a cell containing the value 9 and it is formatted as numeric with 1 decimal place, it will display as 9.0 If I then Copy and Paste_values to a cell formatted as text it will display as 9. What I need is for it to display as 9.0 in the text formatted cell. What I'm after is a general purpose routine that will put the value in one cell as it is displayed into another text formatted cell. Thanks anyway.

BazLondon

 

[HaHoBe]

Hi, BazLondon,

so only that person can give more information?

It depends on where you want to use the code - if it´s only one workbook the code goes into a module. If you need it in differnet workbooks (and only you should utilze it) the personal macroworkbook (or an addin) should be appropriate.

Sub ConvertToStringsPara(strSource As String, strAim As String)
'last line with data form the bottom up
Dim lngLastRow As Long
'used as a counter to step through all the rows
Dim lngCounter As Long

'flicker out
Application.ScreenUpdating = False
'get the number of the last filed visible row from the bottom up.
'no check if the last cell in the column is filled where a 1 would be noted
lngLastRow = Cells(Rows.Count, strSource).End(xlUp).Row
'change the number of the rows where the code should start running
'this could as well as the name of the worksheet be passed as a parameter.
'here we start right at the top with row 1
For lngCounter = 1 To lngLastRow
  'the contents of the cell in the respective row and "aim" column will
  'be strictly put to show text by the leading '. As we want the displayed value
  'of the cell we refer to that cells .Text instead of .Value
  Cells(lngCounter, strAim).Value = "'" & Cells(lngCounter, strSource).Text
'loop through the range
Next lngCounter
'flicker on
Application.ScreenUpdating = True
End Sub

Sub CallUp()
'---------------------------------------------------------------------------------------
' Module    : Modul1
' Author    : HaHoBe
' Date      : 11.12.2009
' Purpose   : Changes the cell value to text in a different column
' Parameters: strSource - the column withg the data. As cells accepts both
'                         a letter as well as a number for the column either
'                         "A" or 1 wikk correspond for the first column of the active sheet
'             strAim    - indicates the column where the data should be put and
'                         shown as text
'             As no further indication has been made could will be run on the
'             active workbook and the active worksheet
'---------------------------------------------------------------------------------------

ConvertToStringsPara "A", "F"
End Sub

This caller is a bit of restricting as it has the columns given as statics - things may be made more universal by using Appicatiuon.Inputbox(type:=8) to get the information about the source and the aim (maybe as well the starting row).

Ciao,
Holger