PDA

View Full Version : [SOLVED] Removing last six characters from a string



Beatrix
02-05-2014, 11:36 AM
Hi Everyone

I've got a list of string data and need to remove last 6 characters from each string. That 6 character might include space, bracket, number etc. Does anyone know any sub procedures for this??

Your help is much appreciated.
Cheers
B.

Beatrix
02-05-2014, 12:09 PM
I've thought I need to clarify the issue with some samples. Please see below:

North (6)
West (7)
East (6)

This regions are listed in a specific column. Say column C and I would like to be able to delete the last 4 (mentioned 6 characters earlier. sorry) charecters- space,number,brackets from the string data in selected column. Is there any sub procedures for this?


Hi Everyone

I've got a list of string data and need to remove last 6 characters from each string. That 6 character might include space, bracket, number etc. Does anyone know any sub procedures for this??

Your help is much appreciated.
Cheers
B.

Kenneth Hobs
02-05-2014, 01:20 PM
A simple formula in a helper column may suffice.
e.g. For data in column A, the formula for B1 and copy down is:
=LEFT(A1,LEN(A1)-4)

snb
02-05-2014, 03:19 PM
or


[c1:c1000]=[if(C1:C100="","",left(C1:C1000,len(C1:C1000)-4))]

Beatrix
02-05-2014, 03:40 PM
Hi Mr Hobs,

Many thanks for your reply. The thing is I am working on a process development for a dynamic reporting system and users need to process multiple workbooks. There will be new workbooks all the time so I was thinking if I could apply this formula through a sub procedure? I mean when I run the script it inserts a helper column then runs this formula then removes the helper column. If the only user was me then this formula would be sufficient :o)


A simple formula in a helper column may suffice.
e.g. For data in column A, the formula for B1 and copy down is:
=LEFT(A1,LEN(A1)-4)

Kenneth Hobs
02-05-2014, 06:42 PM
Using the evaluate method that snb detailed:

Sub ken()
ThisWorkbook.Names.Add "n", Range("C2", Range("C" & Rows.Count).End(xlUp))
[n] = [if(len(n)<4,n,left(n,len(n)-4))]
ThisWorkbook.Names("n").Delete
End Sub

snb
02-06-2014, 01:54 AM
referring to the worksheet could make this code more robust:


Sub M_snb()
Sheet1.Usedrange.columns(2).offset(1).name="n"

[n] = [if(len(n)<4,n,left(n,len(n)-4))]

Names("n").Delete
End Sub

Beatrix
02-06-2014, 05:07 AM
Thanks very much Mr Hobs:bow: it's working perfectly! If there are blank cells in that column it replaces them with zero because of the formula. Is it possible to add an If statement to keep the blank cells as it is ? Also how can I edit the script to apply this formula to all worksheets in ThisWorkbook?

Regards
B.



Using the evaluate method that snb detailed:

Sub ken()
ThisWorkbook.Names.Add "n", Range("C2", Range("C" & Rows.Count).End(xlUp))
[n] = [if(len(n)<4,n,left(n,len(n)-4))]
ThisWorkbook.Names("n").Delete
End Sub

Beatrix
02-06-2014, 05:12 AM
Hi snb ,

I tested your code but couldn't get it to work. It might be me. Does columns(2).offset(1) refer to column B? I need to get it work for column C and tried to change cell address by increasing columns() but didn't work :o)




referring to the worksheet could make this code more robust:


Sub M_snb()
Sheet1.Usedrange.columns(2).offset(1).name="n"

[n] = [if(len(n)<4,n,left(n,len(n)-4))]

Names("n").Delete
End Sub

Kenneth Hobs
02-06-2014, 07:17 AM
Obviously, you need to try code on backups of files.

Sub ken2() Dim ws As Worksheet
ActiveWindow.DisplayZeros = False
For Each ws In Worksheets
ThisWorkbook.Names.Add "n", ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp))
[n] = [if(len(n)<4,n,left(n,len(n)-4))]
ThisWorkbook.Names("n").Delete
Next ws
End Sub

Beatrix
02-06-2014, 08:03 AM
Thank you so much Mr Hobs. Yes I tested them on backup files:yes Now it runs for all ws in ThisWorkbook however it removes the zeros for only active window. Is it possible to include below line in For Each Loop to apply that line for all ws?:think:


ActiveWindow.DisplayZeros = False



Obviously, you need to try code on backups of files.

Sub ken2() Dim ws As Worksheet
ActiveWindow.DisplayZeros = False
For Each ws In Worksheets
ThisWorkbook.Names.Add "n", ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp))
[n] = [if(len(n)<4,n,left(n,len(n)-4))]
ThisWorkbook.Names("n").Delete
Next ws
End Sub

Kenneth Hobs
02-06-2014, 10:18 AM
The forum's code tag moved the Dim line up so I guess you figured that out in ken2. Now that I have my 2nd diet coke...

Sub ken3()
Dim ws As Worksheet, c As Range
For Each ws In Worksheets
For Each c In ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp))
With c
If Not Len(.Value) < 4 Or .Value = "" And VarType(.Value) = vbString Then _
.Value = Left(.Value, Len(.Value) - 4)
End With
Next c
Next ws
End Sub

Beatrix
02-06-2014, 11:06 AM
That's perfect! I noticed that the way the formula set has slightly changed as there is If not statement now. I wouldn't be able to do this by myself at least not now maybe years years later but I have better understanding on vba scripts since I started to use them. Yes I figured it out, I mean about the Dim line.:yes
Thanks very much Mr Hobs.

I am not sure if I have a chance to go for the 3rd requirement? I need to copy this script in a blank workbook for users to run from there and apply to the workbooks in a specific folder. Would this change the script a lot ?: pray2:

I should have thought about this at the beginning. It's my mistake.:doh: Sorry about this. I promise no more questions for this thread.



The forum's code tag moved the Dim line up so I guess you figured that out in ken2. Now that I have my 2nd diet coke...

Sub ken3()
Dim ws As Worksheet, c As Range
For Each ws In Worksheets
For Each c In ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp))
With c
If Not Len(.Value) < 4 Or .Value = "" And VarType(.Value) = vbString Then _
.Value = Left(.Value, Len(.Value) - 4)
End With
Next c
Next ws
End Sub

Kenneth Hobs
02-06-2014, 11:50 AM
Yes, I noticed that from the other thread. This is a batching process which is a fairly common request. Are there files in subfolders that need to be processed? Does the user need to select the parent folder to start processing?

Beatrix
02-06-2014, 02:20 PM
I didn't know it's called a batching process. I've learnt something new.Thanks very much Mr Hobs. Yes there are sub folders and I will save 3 scripts into a new workbook. Each script would open to the related sub folder and process the files in the sub folders. Does parent folder mean where I save the master workbook with the scripts? Is that right? Sorry I realised I know what I want to do but I don't know the terminology :o)



Yes, I noticed that from the other thread. This is a batching process which is a fairly common request. Are there files in subfolders that need to be processed? Does the user need to select the parent folder to start processing?

Kenneth Hobs
02-06-2014, 02:31 PM
It just depends on your file organization. You can have files in the parent folder and/or subfolders. e.g.
c:\invoices has MasterInvoices.xlsm (this would be the one to import the data to from the subfolder files
c:\invoices\2014\January has InvoicesDate20140101.xlsm, InvoicesDate20140102, xlsm, etc.
c:\invoices\2014\February has InvoicesDate20140201.xlsm, InvoicesDate20140202, xlsm, etc.

Beatrix
02-06-2014, 03:06 PM
I see. In my case I created 4 folders ;

1. RemoveColumnsRows ( I'll copy multiple workbooks which need to be processed to delete columns and rows on condition)
2. RemoveCharactersFromRegions (This is your script Mr Hobs. I'll copy multiple workbooks which has region names need to be modified)
3. CalculateQuartiles (the ones need to be processed to make some quartile calculations)

4. and I'll create a workbook named macros with 3 macro buttons and will save those 3 scripts in here. Each button will direct to the related folder.
In this case which one is the parent folder? I google about batching process. I have better understanding. Terminology is really important as it sounds very professional. I need to read a lot more about this area.


It just depends on your file organization. You can have files in the parent folder and/or subfolders. e.g.
c:\invoices has MasterInvoices.xlsm (this would be the one to import the data to from the subfolder files
c:\invoices\2014\January has InvoicesDate20140101.xlsm, InvoicesDate20140102, xlsm, etc.
c:\invoices\2014\February has InvoicesDate20140201.xlsm, InvoicesDate20140202, xlsm, etc.

Beatrix
02-07-2014, 09:15 AM
Hi Mr Hobs ,

I've learned how to run the scripts from parent folder and tested it on your script. Please see below. That worked! I love vba and vbax :cloud9:

Thanks for all your help with this thread.

Regards
B.




Sub ken3()
Dim ws As Worksheet, c As Range

mypath = "C:\vba\" ' change to suit
fName = Dir(mypath & "*.xls") ' change file extension to suit
Do While Len(fName) > 0
Set wb = Workbooks.Open(mypath & fName)

For Each ws In Worksheets
For Each c In ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp))
With c
If Not Len(.Value) < 4 Or .Value = "" And VarType(.Value) = vbString Then _
.Value = Left(.Value, Len(.Value) - 4)
End With
Next c
Next ws

wb.Close True ' to save or false to close without saving
fName = Dir ' get next filename
Loop



End Sub





Yes, I noticed that from the other thread. This is a batching process which is a fairly common request. Are there files in subfolders that need to be processed? Does the user need to select the parent folder to start processing?