Converting Excel Data to formatted Text

Sometime we will need the excel data to be converted to properly formatted text. 
This happens mainly if you are working in mainframe, where we might need to convert a excel file into text and upload it for file processing.
We could copy-paste the excel document on to a notepad. However the formatting will be off. We might actually need the data is specific format and each data element might be having predefined format and length. 

Here is the formula sample that you could use for formatting the columns

Column	Format	Length
A	Text	10
B	Number with leading zeroes	5
C	Text	20
D	Date in YYYYMMDD format	8

So to format the first row you could us the following formula

=LEFT(A1&REPT(" ",10),10)&RIGHT(REPT("0",5)&B1,5)&" "&LEFT(C1&REPT(" ",20),20)&" "&TEXT(D1,"YYYYMMDD")

Notes

LEFT function allows you to extract a substring from a string, starting from the left-most character
RIGHT function allows you to extract a substring from a string, starting from the right-most character
REPT function returns a repeated text value a specified number of times
TEXT function returns a value converted to text with a specified format.

Excel VBA ternary function

Excel VBA doesn't provide any ternary operator or function. Many a times it will be helpful to have one instead of going with If...else... statement which takes up at least 5 lines. 

So here is how we could create a function to simulate the ternary operator

Public Function iff(condCheck As Boolean, ifTrue As Variant, ifFalse As Variant) As Variant

    If condCheck Then

        iff = ifTrue

    Else

        iff = ifFalse

    End If

End Function

A sample code which uses this function

Dim myStatus as String
'Here we are checking for A2 cell. If it is blank we will assign myStatus with N/A else we will assign it with the cell content

myStatus = iff(ActiveSheet.Range("A2")="","N/A",ActiveSheet.Range("A2"))

Intro

Hi All

In this blog, I am planning to post several tips that helped me during my IT career.
The tips will range from languages like COBOL, JCL, VBA, Rexx etc.

Hope the tips will be useful for you in solving some of the issues that you are facing

Thanks
Suvin