NVIT
asked on
Move Prefix word to left field and strip suffix string
For the entire worksheet, I need the first word of field Project Title moved to the left field (PN).
Then, from Project Title, remove the trailing suffix
so it looks like this...
I have a sample excel file attached.
Sample_MoveNumToNextFldStripTail.xlsx
Then, from Project Title, remove the trailing suffix
so it looks like this...
I have a sample excel file attached.
Sample_MoveNumToNextFldStripTail.xlsx
ASKER
Thanks Subodh Tiwari (Neeraj),
It does move the first word of field Project Title to the left field (PN).
Can you have it also remove the trailing suffix from Project Title?
It does move the first word of field Project Title to the left field (PN).
Can you have it also remove the trailing suffix from Project Title?
What do you mean by trailing suffix here?
Would you please show an example of what you are trying to achieve?
Would you please show an example of what you are trying to achieve?
ASKER
Okay try this.....
Sub MovePrefix()
Dim lr As Long
Dim rng As Range, cell As Range
Dim Prefix As String
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("C2:C" & lr)
For Each cell In rng
If cell.Offset(0, -1) = "" Then
Prefix = Left(cell.Value, InStr(cell.Value, " ") - 1)
cell.Offset(0, -1) = Prefix
cell.Replace Prefix & " ", ""
cell.Replace " / total Total Fee", ""
End If
Next cell
Application.ScreenUpdating = True
End Sub
ASKER
Hi Neeraj,
It has an error partway. I've attached relevant images and another Excel file.
SampleWithError.xlsx
It has an error partway. I've attached relevant images and another Excel file.
SampleWithError.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works, Neeraj!. Thanks so much.
You're welcome. Glad to help.
Open in new window