Alphanumeric string manipulation

[alan2013]

I guess ‘Queries’ is the best section to post this to.
I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible.
I think that all of the possible permutations are summed-up by the following examples :

123 A text string
2-8 Another text string
A-C Another text string here
3-20 And some more text
3A-126B More text
Some text without any numbers or letters at the left

What I need to do is :

Move the alphanumeric data at the left to the right. So the data would end up looking like this :

A text string 123
Another text string 2-8
Another text string here A-C
And some more text 3-20
More text 3A-126B
Some text without any numbers or letters at the left

I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and

Is this possible, programmatically ?

Thank you in advance for any help offered.

Best wishes,
Al

 

[Brianwarnock]

There is no difference between the data as originally presented and how you say you would like it.

String manipulation functions are

Instr
Revinstr
Left
Right
Mid
Len

Brian

 

[gemma-the-husky]

assuming there is always a space then this sort of aircode

newstring = mid(instr(startstring," ")+1) + " " & left(instr(startstring," ")-1)


[edit - note that this won't deal correctly with your last example - with no prefix section]

 

[Brianwarnock]

No point in posting any code when the op's post had no spaces in the strings and the original and desired strings are identical. We need more if we are to do it, but better that we give him the tools as I did rather than do his work for him by guess work.

Brian

 

[gemma-the-husky]

brian

he is trying to move the start (hyphenated-code-like) bit of the string to the end. the space is after the hyphenated bit.

 

[Brianwarnock]

brian

he is trying to move the start (hyphenated-code-like) bit of the string to the end. the space is after the hyphenated bit.

Are you sure? It doesn't look certain on my iPad and of course I cannot move through it to check, but anyway the before and after comment still applies. I am amazed that he has not come back, it is surprising how many posters immediately disappear and don't return for ages.

Brian

 

[Brianwarnock]

Lol I've just realised that I have totally misread his post, I feel like an idiot.


Brian

 

[Brianwarnock]

He will need to test for the situation where there is no alpha numeric string, how he does this will depend on the data string. It is probable that a function will need to be written to cover all the checks, perhaps the alphanumeric string will always contain numerics or a hyphen.

Brian

I notice that Dave has edited his post to recognise this problem

 

[Brianwarnock]

Idly sipping coffee, the weather is lousy, I decided to boot up the old PC and have ago at this. The code below works on the assumption in normal alpha strings hyphens are surrounded by spaces eg Smith - Jones.

Brian

Function newstr(ostr As String) As String
Dim firstblock As String

firstblock = Left(ostr, InStr(ostr, " ") - 1)

If InStr(firstblock, "-") = 0 And Not IsNumeric(firstblock) Then
    newstr = ostr
    Exit Function
End If
newstr = Mid(ostr, InStr(ostr, " ") + 1) & " " & firstblock

End Function