Manipulate a string 5

[Mollethewizard]

I need to manipulate a string. Let’s say the sting goes like this: Str1 = “bertram, karl-axel”. I would get rid of the “,” and change places with bertram and karl-axel + proper case on EVERY word. The result should look like this: Karl-Axel Bertram. Any code suggestions?

 

[asjeff]

Hello Mollethewizard - try something like this:

Dim str1 As String
Dim UName As String

str1 = “bertram, karl-axel”

UName = Right$(str1, (Len(str1) - (InStr(str1, ",&quot + 1))) & " " & Left$(str1, (InStr(str1, ",&quot - 1))


Asjeff

 

[Loomah]

Hi
There's bound to be an easier way but this should work

Sub a()
Dim strOrig As String
Dim strOne As String
Dim strTwo As String
Dim strThree As String
Dim strFin As String
Dim iComPos As Integer

strOrig = Range("A1").Text
iComPos = InStr(strOrig, ",")
strOne = Trim(Left(strOrig, iComPos - 1))
strTwo = Trim(Right(strOrig, Len(strOrig) - iComPos))
strFin = strTwo & " " & strOne
If InStr(strFin, &quot;-&quot;) <> 0 Then
    strThree = StrConv(Right(strFin, Len(strFin) - InStr(strFin, &quot;-&quot;)), vbProperCase)

    strFin = StrConv(Trim(Left(strFin, InStr(strFin, &quot;-&quot;))), vbProperCase) & strThree
End If

Range(&quot;B1&quot;) = strFin
End Sub

You'll obviously want to look at working with dynamic cell refs but I'm a bit pushed and have only tested this on the one example you posted.

Another lunchtime special!!
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[Mollethewizard]

Thanks!

Any suggestions how to make upper case for the first letter in each word?

 

[Loomah]

small amendment to my code

If InStr(strFin, &quot;-&quot;) <> 0 Then
    strThree = StrConv(Right(strFin, Len(strFin) - InStr(strFin, &quot;-&quot;)), vbProperCase)
    strFin = StrConv(Trim(Left(strFin, InStr(strFin, &quot;-&quot;))), vbProperCase) & strThree
Else
    strFin = StrConv(strTwo & &quot; &quot; & strOne, vbProperCase)
End If
Range(&quot;B1&quot;) = strFin

Also, out of habbit I have assumed Excel as the app!!

;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[TonyJollans]

Hi Mollethewizard,

As the solutions so far have shown, swapping the two halves of the string around is relatively straightforward but both have possible limitations with proper casing and maybe with excess spaces if your data contains any.

The "PROPER" worksheet function is the one I think which does what you want and I would suggest the following:

WorksheetFunction.Proper(Trim(Right$(str1, (Len(str1) - InStr(str1, ",")))) & " " & Trim(Left$(str1, InStr(str1, ",") - 1)))

Or, if you want to do it in a worksheet directly and your name is in A1 ...

=PROPER(TRIM(RIGHT(A1, LEN(A1) - (FIND(",",A1)))) & " " & TRIM(LEFT(A1, FIND(",",A1) - 1)))

Enjoy,
Tony

 

[Loomah]

Told you there had to be an easier way!

Have one of those purple things on me!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[TonyJollans]

Wow, what can I say! Undeserved, I suspect, but many thanks.

Tony

 

[Mollethewizard]

What can I say? Only Many Thanks for all your effort!

Mollethewizard

 

[bryanbayfield]

Since Mr/ Mrs Wizard has had their problem solved, there's something about strings which I'd like to ask because it's bugging teh you-know-what out of me.

Tony has used the Left$ function. I always thought that you should use the Left function instead as this could handle Null values. That's because (apparently) Left is a function which returns a variant (string) whereas Left$ returns a plain string.

However, I've just tried to get an error in Excel by passing a null value to Left$ and it doesn't have a problem. Was I testing it incorrectly (I referenced an empty cell as the argument) or have I been mislead by msn (as if!)

A bit of a chin-stroker.

 

[xlbo]

Bryan - let me 1st just make sure everyone knows we are talking about Access here and NOT excel

From Access help:
Left(string, length)

If 0, a zero-length string ("&quot is returned. If greater than or equal to the number of characters in string, the entire string is returned.

This seems to indicate that Left on it's own can handle Nulls. I think that the only difference is the data type that is returned. The only advantage of using Left$ that I can think of is that if it is a variant, you can more readily apply calculations to it. In some instances, VBA has been known to throw a wobbler when trying to base a calc on a string and you then have to apply the Val function to the string before doing the calculation. I don't think you would have to do this if you used Left$ and returned a variant.

As the Access help file says:
The Variant versions are more convenient because variants handle conversions between different types of data automatically. They also allow Null to be propagated through an expression (If you try and use string, you won't get an error but NULL will convert to "&quot. The String versions are more efficient because they use less memory. Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bryanbayfield]

Why Access and not Excel Geoff? Also, I get teh impression we're at cross-purposes - Left should be able to handle nulls, it's Left$ I've read as having problems with them.

Me confused!

 

[xlbo]

'pologies - never seen it in excel - only in Access - having now had a look, I can see it's available in VBA (I always thought it was an Access thing)
Also 'pologies for mixing up the data types / functions
You are correct in that $ returns the string. However, my main point still stands - Left$ will not PROPOGATE a null but it won't error either - a NULL will just be converted to "" which is the string representation of a null. Once this has been done, it is very hard to convert back to null. Hence, if you need to pass NULLS through functions, use Left, if not, save memory and use Left$ Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bryanbayfield]

Gotcha.

I can't imagine there's too much call for Left I've never anted anything other than a string returned when I've manipulated another string.

Cheers.

 

[RobBroekhuis]

Gosh, and here I though left$ and right$ etc were just holdovers from ancient versions of Basic that served no real purpose anymore (besides confusing newbies with dollar signs). Learn something new every day...
Rob

 

[TonyJollans]

Hi All,

I seem to have started quite a discussion here! Firsty let me admit to having used Left$ here simply because I took Asjeff's earlier post as a base.

The differences between the two functions are subtle and, as I understand it, only come into play if the input to them is null. As the input here is a string, the dollar functions seem to be the correct ones to use.

In this instance, if the input were null, the Instr function would return null which would cause both Left and Left$ to error with "invalid use of null". If there is any chance of it being null it really should be trapped earlier (are you still listening Mollethewizard?).

To address a couple of points raised ..

Bryan - I don't think an empty cell is null, it is empty and there is a difference.

Geoff - Nulls do not automatically get converted to empty strings and Left$ will error if is passed a null (in my 97 anyway).

Enjoy,
Tony

 

[TonyJollans]

Good Morning All,

I thought about this overnight and nulls are a relational database concept; I don’t think they existed before Codd and Date, twenty-odd years ago.

Excel has to be able to deal with nulls but they do not occur anywhere naturally within it. Unless nulls are being imported, or explicitly created, they should not need to be considered, so applications which are purely Excel-based should be able to use, for example, Left and Left$ more or less interchangeably.

Enjoy,
Tony

 

[xlbo]

Tony - some very good points and I agree - I was testing with empty cells which don't give errors with Left$ (although it is important to note that these functions are not available in worksheets)
And you are also correct that it is very hard / impossible to generate a null value in excel - empty cells are not null

The reason I posted what i did is because I assumed null = empty which is not correct
Note to self - never assume (it'll make an ass outa u and me both ;-) )
emptys do however get converted to empty strings (hence the reason you can test for blank / empty cells with cell.value = "&quot Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bryanbayfield]

I know, I tried to set a variable to Nothing, then take a

Left

portion of it, to which VBA complained that the variable hadn't been set to anything!

You have to wonder what the point is. Although presumably string functions are part of the core language, so Left has to be retained because of it's use with Access, you'd think that the Help files would make some sort of reference to Left being more or less pointless in Excel with Left$ being preferable. The only worthy mention is for Null strings, but that's not very detailed.

Perhaps for teh core lenguage the Help files aren't context specific, ie don't relate to the application in which VBA is embedded.

 

[RobBroekhuis]

I don't understand what the big deal is about Left. I've never used Left$ (since my Basic days). I simply can't think of an instance in which I'd try to apply the Left function to an object, much less an object that is Nothing. So to me the differences are mainly technicalities. If either of the two would go, I'm sure it would be Left$, since that is the one that's there as a legacy (no "modern" VB functions use the string $ suffix).
I must admit I did not realize there was even a difference between the two, so this has been an enlightening thread...
Rob