Concatenation (1 Viewer)

sebekkg

New member
Local time
Today, 00:44
Joined
Apr 22, 2017
Messages
4
Hello guys can you help me on one issue.

For example i have some text fields that i want to represent differently.

They are like this example z564640 54331 but i want them to be 8 in length and if they are shorter it needs to add one or more 0 in front

So from z564640 to 0z564640 and 54331 would be 00054331.

Thanks in advance.

Sent from my SM-N9005 using Tapatalk
 

June7

AWF VIP
Local time
Yesterday, 23:44
Joined
Mar 9, 2014
Messages
5,424
Formatting a number to prefix with 0's is simple with: Format(54331, "00000000")

However, throwing in alphas fails.

Will there always be 7 characters when an alpha is present? Maybe:

0 & Format([Fieldname], "0000000")

Manipulating text requires consistency in structure. If it gets much more complicated (inconsistent structure), build a custom function.
 

sebekkg

New member
Local time
Today, 00:44
Joined
Apr 22, 2017
Messages
4
It is numeric or alfa numeric. "Z" in front or "hz" in front.

Sent from my SM-N9005 using Tapatalk
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:44
Joined
Feb 19, 2002
Messages
42,981
I would create a function because the IIf() will get pretty complex. If you prefer an IIf(), you can convert the logic below. The Space() function returns the number of spaces specified by its argument. There is no function that returns a string of 0's or other characters. So, after you comcatemate the spaces, convert the spaces to 0's.

Code:
Public Function Add0Prefix(InputVal as variant) as String
    Dim InLength As Int
    Dim WorkVal As String
    InLength = Len(InputVal)
    If InLength < 0 Then
        WorkVal = Space(8 - InLength) & InputVal
        WorkVal = Replace(WorkVal, " ", "0")
        Add0Prefix = WorkVal
    Else
        Add0Prefix = InputVal
    End If
End Function
 

June7

AWF VIP
Local time
Yesterday, 23:44
Joined
Mar 9, 2014
Messages
5,424
Actually, there is. Just remembered some code I have used for this.

Let x represent your field.

String(8-Len(x), "0") & x
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:44
Joined
Feb 19, 2002
Messages
42,981
Thanks, I must have been looking at an outdated list.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:44
Joined
Feb 28, 2001
Messages
27,001
Let's say that the magic number of characters you need is represented by MN and the input string is INPS and the output string is OUTPS

Code:
OUTPS = Right( ( String( MN, "0" ) & Trim$( INPS ) ), MN )

This should work correctly even if INPS is null or an empty string or a sequence of blanks. I added a technically unneeded pair of parentheses just to clarify the expression grouping.
 

Users who are viewing this thread

Top Bottom