Adding spaces to long text

[Scott B. Hogle]

This has been driving me nuts all morning.

Here's the scenario:

Our WHQ office wants our clients to program their Credit
Card "swipe" readers so they will accept a Corporate Gift
Card.

The Excel file provided by WHQ includes a 16 character
number that our clients need to enter into their credit
card readers. (Each client has a different number) The
number is all "scrunched" together making it difficult to
read.

You're thinking, no problem, just use "custom" formatting
to force a dash or space between every fourth character.

Great idea, but it wont work - the file from WHQ lists
the 16 character number as TEXT.

Your thinking, no problem, just convert the TEXT to VALUE
and then you can use "custom" formatting.

Great idea, but it won't work - the 16 character value is
so large that Excel enters it as a Scientific Number.
When you convert it back to a normal number, it rounds
off the last value. (i.e. it either rounds up or down
the last value, so .... .... .... 2683
becomes .... .... .... 2680)

I give up - I know it would be easier for my clients to
program their machines if the number were in
an "unscrunched" format, but I can't figure out how to do
it.

I'm very receptive to any possible solutions.

Thanks for reading this long post and have a great day.


SBH

 

[Frank Kabel]

Hi
try the following
=LEFT(A1,4) & "-" & MID(A1,5,4) & "-" & MID(A1,9,4) & "-" & RIGHT(A1,4)
and copy down for all rows

A VBA solution could be the following (for column A)

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "A")
if len(.value) = 12 then
.value = Left(.value,4) & "-" & Mid(.value,5,4) & "-" &
_
Mid(.value,9,4) & "-" & Right(.value,4)
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub

 

[Doug]

How about...

strVar="1234567890123456"
strVar = Left(strVar,4) & "-" & Mid(strVar,4,4) & "-" & Mid
(strVar,8,4) & "-" & Right(strVar,4)

....or something like that???

 

[Bob Phillips]

Scott,

Here's a UDF you can maybe use. You can specify a mask to use on the text.

Use like

=ReFormat("1231231231231236","xxxx-xxxx-xxxxxx-xx")

Function ReFormat(inp, mask As String)
Dim ipos As Long
Dim cHyphens As Long
Dim iHyphen As Long

ipos = Len(mask)
cHyphens = Len(mask) - Len(Replace(mask, "-", ""))
Do
ipos = InStrRev(mask, "-", ipos)
If ipos > 0 Then
iHyphen = ipos - cHyphens
inp = Left(inp, iHyphen) & "-" & Mid(inp, iHyphen + 1, 32)
cHyphens = cHyphens - 1
ipos = ipos - 1
End If
Loop Until ipos = 0
ReFormat = inp
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Scott B. Hogle]

Thanks to all - you guys are awesome!

I ended up using Frank's solution and it worked great.

Thanks again and have a great week.

Scott B. Hogle