How to erase spaces from C behind the text?

[Romano_odK]

Good afternoon,

In the below table I have 6 spaces behind the "location" text in column C. Is there a way to delete all off them in an instance. This sheet is a part of 2000 lines, so that's why I ask.

Thank you for your time.

Text 1	Location	3	Text 4	5	Itemcode	6	Text 7	Query line
update voorrd set maglok = '	P02B	'	where artcode =	'	100000	'	and magcode ='1'	update voorrd set maglok = 'P02B ' where artcode ='100000' and magcode ='1'
update voorrd set maglok = '	P04B	'	where artcode =	'	100001	'	and magcode ='1'	update voorrd set maglok = 'P04B ' where artcode ='100001' and magcode ='1'
update voorrd set maglok = '	P02B	'	where artcode =	'	100002	'	and magcode ='1'	update voorrd set maglok = 'P02B ' where artcode ='100002' and magcode ='1'
update voorrd set maglok = '	P04B	'	where artcode =	'	100003	'	and magcode ='1'	update voorrd set maglok = 'P04B ' where artcode ='100003' and magcode ='1'
update voorrd set maglok = '	P02B	'	where artcode =	'	100004	'	and magcode ='1'	update voorrd set maglok = 'P02B ' where artcode ='100004' and magcode ='1'
update voorrd set maglok = '	P01B	'	where artcode =	'	100006	'	and magcode ='1'	update voorrd set maglok = 'P01B ' where artcode ='100006' and magcode ='1'
update voorrd set maglok = '	P03B	'	where artcode =	'	100007	'	and magcode ='1'	update voorrd set maglok = 'P03B ' where artcode ='100007' and magcode ='1'
update voorrd set maglok = '	Q07A	'	where artcode =	'	100008	'	and magcode ='1'	update voorrd set maglok = 'Q07A ' where artcode ='100008' and magcode ='1'
update voorrd set maglok = '	Q07A	'	where artcode =	'	100009	'	and magcode ='1'	update voorrd set maglok = 'Q07A ' where artcode ='100009' and magcode ='1'
update voorrd set maglok = '	Q08B	'	where artcode =	'	100010	'	and magcode ='1'	update voorrd set maglok = 'Q08B ' where artcode ='100010' and magcode ='1'
update voorrd set maglok = '	Q07A	'	where artcode =	'	100011	'	and magcode ='1'	update voorrd set maglok = 'Q07A ' where artcode ='100011' and magcode ='1'

 

[Fluff]

How about

Sub Romano()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate("trim(" & .Address & ")")
   End With
End Sub

 

[Romano_odK]

How about

Sub Romano()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Value = Evaluate("trim(" & .Address & ")")
   End With
End Sub

This definitely did the trick. Thank you and have a great day.

 

[Peter_SSs]

Since it appears there are no spaces in the Location codes, what about just Find/Replace, either with the code below or manually?

Sub Romano_v2()
   Columns("C").Replace " ", "", xlPart
End Sub

BTW, with XL2BB I would recommend using Mini Sheet over Table Only since that more clearly shows what columns/rows are involved.

 

[Fluff]

Glad we could help & thanks for the feedback.