Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I am trying to convert a numbering system in an old dbase file. I have manage to open it in calc and, before I use it as a table in a new database created with base, there is a field (column) I would like to modify.

The actual format is ex : 100-1234-56 I would like to:

add a 1 in front of it, keep the first 3 numbers, remove the dash, keep the next four numbers and remove the dash with the last 2 numbers like this:

100-1234-56 would be 11001234

What are my options ?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

Assuming your first number is in cell A1, enter the following formula into cell B1:

=CONCATENATE("1";MID(A1;1;3);MID(A1;5;4))

The "MID" function extracts a text sub-string from cell A1. For example, the first instance starts at 1 and extracts 3 characters giving the "100" string. Concatenate adds the substrings together so that your new value starts off as 1 + 100, and so on.

share|improve this answer
    
That did it... awesome, now if I want to get rid of Column A, I will lose the data has column B is the formula... How do I get the result to be the value of the cell itself, I tried to do a copy/special paste with only text but it adds a 'before the number, it is not visible in the cell itself but it is in the input line. Will that reflect in my table ? –  Mobidoy Dec 15 '10 at 14:41
    
Now that you have B1, the quick way is to make cell C1 =VALUE(B1) to convert the text string to a number. Now, copy cell C1 and Paste-Special into cell D1 with Selection=Numbers in order to retain the numeric value. Of course, since you wanted the final product to be a number, you could have included the Value function in the initial transformation by making cell B1 =VALUE(CONCATENATE("1";MID(A1;1;3);MID(A1;5;4))) –  CentaurusA Dec 15 '10 at 22:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.