开发者

Converting a number (non-currency) value to a text entry

开发者 https://www.devze.com 2023-04-07 00:29 出处:网络
Easy enough concept, but I have no idea where to start when it comes to creating a UDF, which is the only thing I can开发者_如何学Go find any mention of.I have a column that populates on source sheets

Easy enough concept, but I have no idea where to start when it comes to creating a UDF, which is the only thing I can开发者_如何学Go find any mention of. I have a column that populates on source sheets with either a 1 or 2. I want to do something so that all of the "1's" shows as one text entry("AA" for example) and all "2's" show as a different entry(say "BB"). Is this possible without a UDF; and if not then is there any advice on where to start?


You can use custom formatting for this. Right-click the column in question and choose "Format Cells." In the dialog, choose "Custom" and in the box at the top enter:

[=1]"AA";[=2]"BB";General

This assumes that the "1" or "2" is the sole content of the cell. Any other number or text will display in the General format.


This may help you as well. It is a conditional statement that will reference one cell the check if there is content, if not then it will put the word "None" in, otherwise it will put the contents of the cell.

=IF((Sheet1!J1089)="","None",Sheet1!J1089)


Just to update anyone else that may be interested. I have a solution that I am using. Had to go the vba route, but I've got it set up so that my macro for running reports runs the following:

Sub Conversion()
  Dim X As Long, DBCodes() As String
  DBCodes = Split("AA,BB,CC", ",")
  For X = 1 To 3
    Columns("H").Replace X, DBCodes(X - 1), xlWhole
  Next
End Sub

I can change the split values and the line after for as many more values as I need replacing, though it would take fiddling with to find the point where too many values would make it impractical. Also, it makes a world of difference where I put in the line to run this; found the best spot though and even the reports that are 600+ rows the conversion only adds a couple seconds.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号