Removing last character in a cell, with varying word lengths

[DataQuestioner]

I need to remove the last character in a cell, which is always a "?" - the single words in each cell will have varying lengths, and can commence with a upper or lower case letter, and will contain any of the 26 letters of the alphabet, which could also include a hyphen, e.g.

"day-dream?" should become "day-dream"
"really?" should become "really"
"Unbelievably?" should become "Unbelievably".

I've tried experimenting with "IF" "LEFT" "RIGHT" and "MID" functions, but I haven't got my head around their proper usage yet!

Please help if you can.

 

[jsotola]

select cells with mouse ... press CTRL-H ... find what ? ... replace what leave blank ... replace all


enjoy

 

[VBA Geek]

assuming your word is in A1 then

Cell B1= left(A1, len(A1)-1)

 

[fibonacci1101]

=IF(RIGHT(A1,1)="?",LEFT(A1,LEN(A1)-1),A1)

Put your word in cell A1.

 

[mikerickson]

select cells with mouse ... press CTRL-H ... find what ? ... replace what leave blank ... replace all
...

? is a wild-card, this process will blank all of the selected cells.
Search for ~? instead

 

[DataQuestioner]

Guys,
Thanks for each of your suggestions...that was quick!

On reflection, I don't know why I didn't use the simple find/replace suggestion made by jsotola.

Thanks for your help.

 

[DataQuestioner]

Thanks Mike.

Yes, I will use the tilde before the ? for the find/replace.

 

[dispelthemyth]

This is the best solution

assuming your word is in A1 then

Cell B1= left(A1, len(A1)-1)

 

[jsotola]

i should have tried it before posting

thanks for the info about the tilde character

 

[hiker95]

DataQuestioner,

Sample raw data in column A beginning in cell A1:

Excel 2007
	A
1	day-dream?
2	really?
3	Unbelievably?
4	day-dream? really?
5	??? DataQuestioner?
6	? hiker95?
7
Sheet1

After the macro:

Excel 2007
	A
1	day-dream
2	really
3	Unbelievably
4	day-dream? really
5	??? DataQuestioner
6	? hiker95
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RemoveLastCharacter()
' hiker95, 12/29/2013
' http://www.mrexcel.com/forum/excel-questions/747357-removing-last-character-cell-varying-word-lengths.html
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c = Trim(c)
  If Right(c, 1) = "?" Then c = Left(c, Len(c) - 1)
Next c
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveLastCharacter macro.