آلن فهرستی از چند صد نام و آدرس دارد. آدرس‌های خیابان از خیابان اصلی، خیابان اصلی 123، US RT 2، یا 187 South Elm St. او می‌خواهد شماره خیابان را از آدرس‌ها مشخص کند. بنابراین آدرس 123 Main Street در یک سلول به "123" و در سلول دیگر "خیابان اصلی" ختم می شود. اگر شماره خیابان وجود نداشته باشد، هیچ چیز به ستون شماره خیابان ختم نمی شود. ابزار Text to Columns کار نخواهد کرد و او تعجب می کند که چگونه می تواند این کار را انجام دهد.
در یک دنیای عالی، اکسل به شما این امکان را می دهد که به راحتی اعداد را از نام خیابان ها جدا کنید. از آنجایی که این گزینه وجود ندارد، شما چند انتخاب دارید. زمان‌برترین گزینه شامل اضافه کردن یک ستون اضافی و تایپ مجدد داده‌ها است. با این حال، اگر می خواهید در زمان خود صرفه جویی کنید، می توانید از فرمول های مختلفی برای انجام کار استفاده کنید.
با فرض اینکه لیست آدرس‌ها در ستون A (شروع سلول A1) باشد، می‌توانید از فرمولی مشابه شکل زیر برای بیرون کشیدن بخش عددی آدرس استفاده کنید:
=IF(ISERROR(VALUE(LEFT(A1,1))),"",LEFT(A1,FIND(" ",A1)-1))
با فرض قرار دادن فرمول در سلول B1، می توانید از فرمول دیگری برای استخراج بخش غیر عددی آدرس استفاده کنید:
=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))
توجه داشته باشید که این رویکرد دارای محدودیت است. برخی از آدرس‌ها، به‌ویژه در کلان‌شهرهای بزرگ، از قالبی مانند بلوار بل 152-33 استفاده می‌کنند. فرمول های بالا برای این آدرس ها کار می کنند، اما اگر جایگزین، بلوار بل 152 33، استفاده شود، فرمول به اشتباه تجزیه می شود. مگر اینکه بخواهید یک برنامه تجزیه آدرس حرفه ای توسعه یافته بخرید، فرمول های بالا و اسکن سریع کره چشم از نتایج باید کافی باشد.
فرمول دیگری در این مورد کار می کند. با فرض اینکه آدرس شما در سلول A2 است، فرمول زیر را در سلول B2 وارد کنید:
=IF(ISNUMBER(VALUE(LEFT(A2,1))),VALUE(LEFT(A2,FIND(" ",A2)-1)),"")
این فرمول می‌گوید: "اگر کاراکتر اول یک عدد نیست، سلول را خالی بگذارید. در غیر این صورت، تمام کاراکترهای سمت چپ را به من بدهید، اما بدون احتساب، اولین فاصله." سپس می توانید از نتیجه این فرمول برای بیرون کشیدن بخش غیر عددی آدرس استفاده کنید:
=IF(B2="",A2,MID(A2,FIND(" ",A2)+1,99))
روش دیگر استفاده از فرمول آرایه است. در اینجا دوباره، با فرض اینکه آدرس شما در سلول A2 باشد، می توانید از موارد زیر استفاده کنید:
=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1)) = TRUE,LEFT(A2,FIND(" ",A2,1)),"")
از آنجایی که این یک فرمول آرایه است، باید آن را با استفاده از Ctrl+Shift+Enter وارد کنید . نتیجه این است که فرمول بخش عددی اصلی آدرس را برمی‌گرداند. سپس می توانید بخش غیر عددی را با استفاده از فرمول آرایه زیر تعیین کنید:
=IF(ISNUMBER(1*MID(A2,ROW($1:$1),1))=TRUE,RIGHT(A2,LEN(A2)-FIND(" ",A2,1)),A2)
در نهایت، ماکرو زیر را می توان برای جدا کردن آدرس خیابان از نام خیابان استفاده کرد.
Sub GetStreetNum()
Dim sStreet As String
Dim J As Integer
Dim iNum As Integer
For Each cell In Selection
sStreet = cell.Value
J = InStr(sStreet, " ")
If J > 0 Then
iNum = Val(Left(sStreet, J))
If iNum > 0 Then
cell.Offset(0, 1).Value = iNum
sStreet = Trim(Mid(sStreet, J, Len(sStreet)))
End If
End If
cell.Offset(0, 2).Value = sStreet
Next
End Sub
برای استفاده از این ماکرو، کافی است محدوده سلول هایی که حاوی آدرس های شما هستند را انتخاب کنید و سپس آن را اجرا کنید. بخش عددی اصلی آدرس در سلول سمت راست هر آدرس ظاهر می شود و موجودی آدرس در سلول سمت راست آن قرار می گیرد. (بنابراین باید مطمئن شوید که دو ستون خالی در سمت راست آدرس هایی که انتخاب می کنید وجود دارد.)
