Strings

[EDUCATED MONKEY]

I have a workbook with a number of strings, in these strings are numbers, also of unknown value, and not always in the same place so no ease way to get at them as far as I can see spent most of the morning trying to using lots of different way but to no avail, the numbers being markers for genres so once I have the location and value I could retrieve the required data <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
My original idea using VBA, I would scan the string using the mid function and test the string letter by letter till I got a match with the numbers 0 through 9 then I would know where it was and its value.<o></o>
<o> </o>
The aim then would be to change it for the @ symbol so next time the string is check by another process I could simply look for the @ symbol and do a mid to retrieve the data there after <o></o>
<o> </o>
So if you can help I would be most grateful<o></o>
<o> </o>

 

[Smitty]

You'll need to post some examples of what you've got and your desired outcome.

 

[EDUCATED MONKEY]

You'll need to post some examples of what you've got and your desired outcome.

ABCDEF2XYZ<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
ABC4EFGXYZ <o></o>
<o> </o>
Iin these two strings the numbers 2 and 4 mark the required data in the case of the first 2xyz where xyz being the data
And in the second 4efgxyz being the data
<o> </o>
Now what I would like to do is this refering again to the two examples
<o> </o>
ABCDEF2XYZ would become ABCDEF@XYZ<o></o>
And
ABC4EFGXYZ would become ABC@EFGXYZ<o></o>

 

[Kenneth Hobson]

Sub test()
  Dim s As String, i As Integer
  s = "ABCDEF2XYZ"
  For i = 0 To 9
    s = Replace(s, i, "@")
  Next i
  MsgBox s
End Sub

 

[T. Valko]

ABCDEF2XYZ<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
ABC4EFGXYZ <o></o>
<o></o>
Iin these two strings the numbers 2 and 4 mark the required data in the case of the first 2xyz where xyz being the data
And in the second 4efgxyz being the data
<o></o>
Now what I would like to do is this refering again to the two examples
<o></o>
ABCDEF2XYZ would become ABCDEF@XYZ<o></o>
And
ABC4EFGXYZ would become ABC@EFGXYZ<o></o>

If you want to use a formula...

Book1
	A	B
2	ABCDEF2XYZ	ABCDEF@XYZ
3	ABC4EFGXYZ	ABC@EFGXYZ
Sheet1

Entered in B2 and copied down as needed:

=REPLACE(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1,"@")

 

[mikerickson]

Here's a non-vba solution

=MAX(IFERROR(MID(A1, COLUMN($A$1:$Z$20), ROW($A$1:$Z$20))+0,0))


entered with Ctrl-Shift-Enter (Cmd+Return for mac)

 

[EDUCATED MONKEY]

this is perfect, I just chose the wrong root to the top of the mountain thank god you were there to show me the way down, it just seemed the longer I had not solved this the harder it was getting dose not help when your brain keeps crashing<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
thanks so much Pete<o></o>
<o> </o>

 

[EDUCATED MONKEY]

this may well come in just handy as the vba version thanks for taking the trouble pete

 

[Biz]

If you want to use a formula...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 91px"><COL style="WIDTH: 98px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; FONT-FAMILY: Arial; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">ABCDEF2XYZ</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">ABCDEF@XYZ</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; FONT-FAMILY: Arial; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">ABC4EFGXYZ</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">ABC@EFGXYZ</TD></TR></TBODY></TABLE>


Entered in B2 and copied down as needed:

=REPLACE(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1,"@")

Very nice formula mate.

Biz

 

[T. Valko]

Very nice formula mate.

Biz

Thanks!