[Solved] Custom Cell format

[Smoberly]

Hello All. I am no expert at Excel, but i can usually get things figure out. I have one that has me stumped. I need to be able to add a number to a cell and then have the following format be automatically filled in. The only other option I would have is to type it all in on all 1300 rows or copy and past and change the number on all 1300 rows which both do not sound like fun at all. See below for the format that it needs to look like when I enter a number (in this case the number would be 1509 and the rest of the info and characters will be the same on every row. I can not add any more columns or pages or anything like that at all due to when I import this back into the system I exported from it will throw errors and not work. Thank you all for any help you can give

{1509~1509~26 no FC~Active~~~}

 

[Yousuf Shovon]

I can not add any more columns or pages or anything like that at all due to when I import this back into the system I exported from it will throw errors and not work.

Hello Smoberly,

Welcome to the ExcelDemy forum. Thanks for the detailed explanation of the issue. I understand you wanted to customize your cell format. Which would be easy using Excel Formula or features. The below article has 17 methods to do so:

How to Custom Format Cells in Excel​

However, you mentioned you can not add any new columns or pages as you want to import back the worksheet into a system to avoid errors. I think using VBA will be suitable in this case. We can use a VBA code to customize the cell formats without adding any new rows or columns.

Read this below article to learn how to use custom cell formatting using VBA:

How to Format Number with VBA in Excel​

This article will show you 4 methods to format numbers using VBA. For your better understanding, I will show you the steps. I have taken 1300 rows of data and formatted them according to the given formats.

• Go to Developer tab >> Visual Basic.

​

Note: If you do not see the Developer tab in your ribbon, read the below article.

How to Display Developer Tab on the Ribbon in Excel​

• Click on Insert >> Module >> copy the below VBA code in the Module box >> close the window.

​

Here is the VBA code:

Sub ApplyFormatToSelectedCells()
    Dim selectedRange As Range
    Dim cell As Range
    Dim outputFormat As String

    On Error Resume Next
    Set selectedRange = Application.InputBox("Select the cells you want to apply the format to.", Type:=8)
    On Error GoTo 0

    If selectedRange Is Nothing Then
        Exit Sub
    End If

    For Each cell In selectedRange
        outputFormat = "{" & cell.Value & "~" & cell.Value & "~26 no FC~Active~~~}"
        cell.Value = outputFormat
    Next cell
End Sub

• Go to Developer tab >> Macros >> select ApplyFormatToSelectedCells >> Run.

​

• In the Input dialog, select the cells you want to format. Select the first cell >> press Ctrl + Shift + Down Arrow to select quickly.

​

• Pressing Ok or Enter will return the desired formatting.

​

Hopefully, you can implement this method in your dataset now. Let us know if it works. I have attached the Excel file here.

Regards,
Yousuf Shovon