Convert tekst to data for calculation

**lentebriesje** · 03-28-2013, 03:25 PM

Hello,

I'm wondering whether it's possible in excel to split the contents of one cell into other cells so it can be used for calculations.

Input:
40.718119,-74.006653 (Longitude and Lattitude)

How i need to make it for the calculations to work:
Output 1
40,718119

Output 2
-74,006653

(dot's to comma's, splitting the two numbers up and getting rid of the devider comma)

Is this possible in any way to do this with a function?

Kind Regards,

**Ron Coderre** · 03-28-2013, 03:30 PM

With
A1: a long,lat value.....40.718119,-74.006653

These regular formulas parse that value into long and lat, replacing decimals with commas

Formula:

Please Login or Register  to view this content.

In the above example, these are the returned values:
40.718119
-74.006653

Is that something you can work with?

**lentebriesje** · 03-28-2013, 05:02 PM

I've tried those exact codes out and they return errors, for one i need to use ";" 's to seperate the function rules. But even then still it doesn't work

i tried to just cut the formula up in pieces and see where it goes south, and it seems to be the FIND function, possibly in conjuction with the LEFT function, because i wasn't sure how the "A1)-1)" part would work.

The substitute function is awesome though, thanks for pointing that one out to me

**WHER** · 03-28-2013, 05:29 PM

Maybe like this:

**lentebriesje** · 03-28-2013, 06:01 PM

that works perfectly! thanks man!

**arlu1201** · 03-29-2013, 01:44 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.