Removing characters from a column

[Neil45156]

Hi Everyone,

Is there a way you can remove / limit the number of characters in a column? I get given an excel file from a database program which I need to send to another program for the Bank. The program the Bank uses will only allow 18 characters in column A.

Is there a way, I can get Excel to look at column A and remove any characters after 18 in column A?

Many thanks

Neil

[benishiryo]

hi Neil, welcome to the forum. if you're talking about extracting 18 characters from an existing data, then:
=LEFT(A1,18)

if you're talking about preventing users from keying more than 18 characters, try selecting column A (maybe A1:A100), go to Data -> Data Validation -> Allow: custom -> Formula:
=LEN(A1)<=18
you can even put an error message in the Data validation window under the "Error Alert" tab

[Neil45156]

Thanks for that, I have tried both those options, but I was lookinmg for something that would remove existing characters in the column and still keep the data in column A.

[TheNewUnion]

So, you want to trim any value/string in colum A to 18 characters, but do it in situ as it were? Is that correct?

Depending on what you want to happen to any columns to the right, you might be able to use "Text to Columns" from the Data menu. Highlight the column then:

Data>Text to Columns then pick Fixed Width.

You can then use the scale to pick 18 characters (just click on the correct marker in the scale), hit Next then finish. 18 characters in Colum A, the rest in Column b , which you can delete as you see fit (if you need to do it a lot, record a simple macro to repeat the steps.

Good luck

[Neil45156]

Thats worked many thanks for your help