Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert blank space
Hi,
I have a variable length data in a cell. I need to be able to insert a blank space after the 3rd character from the right if one does not already exist. How would I go about doing this? Thanks, Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert blank space
Hi Ed,
as this group is about programming, a basic example about string handling. You may to convert it into a function, if you like. Sub Test5bb() Dim sTmp As String Dim sLft As String ' left part Dim sRgt As String ' right part sTmp = "abcdefghijklmno" If Mid(sTmp, Len(sTmp) - 2, 1) < " " Then sLft = Left(sTmp, Len(sTmp) - 2) sRgt = Right(sTmp, 2) sTmp = sLft & " " & sRgt End If MsgBox sTmp End Sub -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert blank space
Try something like the following:
Sub AAA() Dim Rng As Range Dim S As String Set Rng = Range("A3") S = Rng.Text If Len(S) = 4 Then If Mid(S, 3, 1) < " " Then S = Left(S, 2) & " " & Mid(S, 3) End If Rng.Value = S End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Ed Peters" wrote in message ups.com... Hi, I have a variable length data in a cell. I need to be able to insert a blank space after the 3rd character from the right if one does not already exist. How would I go about doing this? Thanks, Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert blank space
On 8 Sep, 10:56, "Chip Pearson" wrote:
Try something like the following: Sub AAA() Dim Rng As Range Dim S As String Set Rng = Range("A3") S = Rng.Text If Len(S) = 4 Then If Mid(S, 3, 1) < " " Then S = Left(S, 2) & " " & Mid(S, 3) End If Rng.Value = S End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consultingwww.cpearson.com (email on the web site) "Ed Peters" wrote in message ups.com... Hi, I have a variable length data in a cell. I need to be able to insert a blank space after the 3rd character from the right if one does not already exist. How would I go about doing this? Thanks, Ed- Hide quoted text - - Show quoted text - Thanks guys! Ed |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert blank space
Hi Ed,
You posted in the programming group so you must want a VBA solution and you got two. Here is a formula solution you may find helpful... or not. =IF(MID(A1,3,1)<" ",LEFT(A1,3)&" "&MID(A1,4,LEN(A1)),A1) Copy Paste Special Values to get rid of the formulas. Regards, Howard "Ed Peters" wrote in message ups.com... Hi, I have a variable length data in a cell. I need to be able to insert a blank space after the 3rd character from the right if one does not already exist. How would I go about doing this? Thanks, Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank space | Excel Worksheet Functions | |||
add blank space in cell if first name is not blank | Excel Worksheet Functions | |||
Insert Space | Excel Discussion (Misc queries) | |||
Insert Tab Space | Excel Programming | |||
I want a blank space on a worksheet when the question is blank? | Excel Discussion (Misc queries) |