Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 20th 20, 01:36 PM posted to microsoft.public.excel.programming
hbj hbj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2011
Posts: 35
Default VBA code to change numeric value to string

The values entered in a column should always be of string type. If the user enters a numeric value it should be changed to string. Example: if the user enters 100 it should be presented as "00100". I have tried a code into Worksheet.Change() with Cells(TargetRow,TargetColumn).value = "00100". The cell value does not change.

Hakan

  #2   Report Post  
Old January 20th 20, 01:39 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,762
Default VBA code to change numeric value to string

Hi Hakan,

Am Mon, 20 Jan 2020 04:36:43 -0800 (PST) schrieb hbj:

The values entered in a column should always be of string type. If the user enters a numeric value it should be changed to string. Example: if the user enters 100 it should be presented as "00100". I have tried a code into Worksheet.Change() with Cells(TargetRow,TargetColumn).value = "00100". The cell value does not change.


why don't you use a custom number format: 00000


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old January 21st 20, 04:53 PM posted to microsoft.public.excel.programming
hbj hbj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2011
Posts: 35
Default VBA code to change numeric value to string

On Monday, January 20, 2020 at 2:39:43 PM UTC+2, Claus Busch wrote:
Hi Hakan,

Am Mon, 20 Jan 2020 04:36:43 -0800 (PST) schrieb hbj:

The values entered in a column should always be of string type. If the user enters a numeric value it should be changed to string. Example: if the user enters 100 it should be presented as "00100". I have tried a code into Worksheet.Change() with Cells(TargetRow,TargetColumn).value = "00100". The cell value does not change.


why don't you use a custom number format: 00000


Regards
Claus B.
--
Windows10
Office 2016


Because that is only a presentation value - the real value will still remain numeric i.e. 100. 00100 is a zip code which will be forwarded to an external mail merging program.

Håkan
  #4   Report Post  
Old January 21st 20, 06:41 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,152
Default VBA code to change numeric value to string

On Monday, January 20, 2020 at 2:39:43 PM UTC+2, Claus Busch wrote:
Hi Hakan,

Am Mon, 20 Jan 2020 04:36:43 -0800 (PST) schrieb hbj:

The values entered in a column should always be of string type. If the user
enters a numeric value it should be changed to string. Example: if the user
enters 100 it should be presented as "00100". I have tried a code into
Worksheet.Change() with Cells(TargetRow,TargetColumn).value = "00100". The
cell value does not change.


why don't you use a custom number format: 00000


Regards
Claus B.
--
Windows10
Office 2016


Because that is only a presentation value - the real value will still remain
numeric i.e. 100. 00100 is a zip code which will be forwarded to an external
mail merging program.

Hkan


Zip Code is a built-in 'Special' format so typing 100 displays as 00100; - why
not use it?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Old January 22nd 20, 10:41 AM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2020
Posts: 3
Default

=TEXT({number},"00000")


  #6   Report Post  
Old January 25th 20, 05:35 PM posted to microsoft.public.excel.programming
hbj hbj is offline
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2011
Posts: 35
Default VBA code to change numeric value to string

On Wednesday, January 22, 2020 at 11:49:15 AM UTC+2, Lepista wrote:
=TEXT({number},"00000")




--
Lepista


Thank you. I'll give it a try.

Håkan


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text string to Numeric string Manikandan[_2_] Excel Discussion (Misc queries) 2 March 12th 09 09:55 AM
string and numeric cosmin Excel Programming 6 August 1st 08 03:03 PM
How do I replace last numeric string from a alphanumeric string? Christy Excel Discussion (Misc queries) 3 August 11th 06 12:17 AM
Find numeric value at end of string Barb Reinhardt Excel Worksheet Functions 13 February 5th 06 12:31 AM
Find first numeric value in text string Rbp9ad Excel Worksheet Functions 4 October 15th 05 02:01 AM


All times are GMT +1. The time now is 01:23 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017