Remove 1st character of string

[EcoWill]

How can one remove the 1st character of a string, so that the string displays without it? ex. change XHow are you? to How are you?
Thanks

 

[Andrzejek]

You can place a cursor between X and H and hit Backspace.

You can do 'Find and Replace' and find 'X' and replace with nothing.

In VBA code you can

Dim str As String
str = "XHow are you"
str = Mid(str, 2)
MsgBox str

You need to be more specific of how do you want to do it...

Have fun.

---- Andy

 

[EcoWill]

These preceding Xs are located at the beginning of every single string in an entire column. There are thousands of them, that were put there by an idiot. Now they need to go away.

 

[EcoWill]

Also - find and replace will remove more than the first letter of the string. The actual letter i am trying to remove is an L not an X, and that is a commonly used letter.

 

[kjv1611]

Assuming you want a formula instead of VBA, you can also do it this way... also assuming the string value is in cell A1:
=MID(A1,2,LEN(A1))

 

[kjv1611]

Or if you want VBA to loop through the whole thing and do it, you'll need to build a loop, put Andrzejek's code within the loop, and variables for the cells. Then link a button to the macro so you can run it every time needed without opening the VB editor or typing the formula.

 

[EcoWill]

Bingo. I do NOT want to use VBA, so the first Kjv1611 formula did the trick. Thanks. Solved.

 

[kjv1611]

If you wanted the button and vba solution, you could do something like this:

Sub DumpFirstChar()
  Dim wb as Workbook
  Dim ws as Worksheet
  Dim x as long 'Row

  Set wb = ActiveWorkbook
  Set ws = wb.ActiveSheet

  For x = 2 To Range("A65536").End(xlup).Row
    ws.Cells(x,1).Formula = Mid(ws.Cells(x,1).Formula, 2)
  Next x

  Set ws = Nothing
  Set wb = Nothing
End Sub

That's untested, putting the previous vba code in a loop. And it's assuming that your column to fix would always be in column A, and that it would always start in Row 2, since you'd likely have a column header (field names).

 

[Andrzejek]

These preceding Xs are located at the beginning of every single string in an entire column

That would be nice to know from the very beginning:
It is Excel (version would be nice, too).

If we could only read other's minds....

Have fun.

---- Andy

 

[mintjulep]

Or just use Data | Text to Columns with the fixed width option, then delete the resultant row of X's or L's or whatever they are.

 

[EcoWill]

Oh. Excel. Yep. OK IT IS EXCEL!!

 

[kjv1611]

EcoWill,

We know you're talking about Excel, Andy's talking about WHICH VERSION of Excel - '97, '03, '07, 2010, etc..

But also... are you getting rid of the 1st character regardless of what it is, or are you ONLY looking for Xs or Ls... or some other letter? You need to be specific to make sure you are getting the correct solution. Even to get a specific end, there may be many different ways to get it.

Did the formula take care of it for you? If so, then that means you want to get rid of the first character, regardless of what it is. If not, then it needs some adjusting.

 

[kjv1611]

Well, I suppose it were possible you were talking about another application, but in this forum, you SHOULD be talking about Excel with columns and rows... Access has its own dedicated forums, and though you can talk rows/columns in other apps, Excel seems the most natural to come to mind.

 

[EcoWill]

As I said above: Problem solved. Next time i will state the version, which in this case is 2010.
The solution is =MID(A1,2,LEN(A1))
Thanks all.

 

[GlennUK]

... or do similar to what mintjulep said

Or just use Data | Text to Columns with the fixed width option, then delete the resultant row of X's or L's or whatever they are.

... exactly that ( split after 1st char only ), then do Next, and when the columns are shown in the next dialog change the column type for the first column to be Do Not Import Column ( Skip ) ... click Finish, and your complete operation is done.

Cheers, Glenn.

Beauty is in the eye of the beerholder.