Link to home
Start Free TrialLog in
Avatar of NVIT
NVITFlag for United States of America

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...

User generated image
I have a sample excel file attached.
Sample_MoveNumToNextFldStripTail.xlsx
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Please 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 & " ", ""
   End If
Next cell
Application.ScreenUpdating = True
End Sub

Open in new window

Avatar of NVIT

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?
What do you mean by trailing suffix here?
Would you please show an example of what you are trying to achieve?
Avatar of NVIT

ASKER

In the original excel attachment Sample_MoveNumToNextFldStripTail.xlsx, each item under  Project Title has a " / total Total Fee" suffix
 User generated image
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

Open in new window

Avatar of NVIT

ASKER

Hi Neeraj,

It has an error partway. I've attached relevant images and another Excel file.
User generated imageUser generated imageUser generated imageSampleWithError.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NVIT

ASKER

It works, Neeraj!. Thanks so much.
You're welcome. Glad to help.